Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Excel CountIf formula


  • Please log in to reply

#1
jdp

jdp

    New Member

  • Member
  • Pip
  • 9 posts
I am trying to create a table from a list of data in Excel. I want to count the number of entries where 1 column meets a certain criteria and another column meets a certain criteria.

For instance:
Countif column a1:a100<=40 and column b1:100<=500

then there will be columns where I want it to count entries between certain numbers

Countif column a1:100>40 but <100 and column b1:b100>1000 but <5000

Each countif statement would be a block in the chart.

So far I am not having much luck.

Thanks,
  • 0

Advertisements


#2
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
JDP,
I think what you are trying to acheive is to count when a pair of numbers meet a set of conditions

eg. count in the following conditions
a1 = 10 and b1 = 400

but dont count in the following conditions
a1 = 50 and b1 = 400
a1 = 10 and b1 = 550
a1 = 50 and b1 = 550


I couldn't figure out a formula to acheive this directly, but you could create a column with the following formula to see if the pair of numbers match the criteria and then sum or countif the results column.
=IF(A2<41, IF(B2<501,1,0),0)


HTH

Regards
David
  • 0

#3
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
JDP,
I think what you are trying to acheive is to count when a pair of numbers meet a set of conditions

eg. count in the following conditions
a1 = 10 and b1 = 400

but dont count in the following conditions
a1 = 50 and b1 = 400
a1 = 10 and b1 = 550
a1 = 50 and b1 = 550


I couldn't figure out a formula to acheive this directly, but you could create a column with the following formula to see if the pair of numbers match the criteria and then sum or countif the results column.
=IF(A2<41, IF(B2<501,1,0),0)


HTH

Regards
David
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP