countif

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Countif

Countif

resolvedResolved · Low Priority · Version 2003

Angelo has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Access Advanced course
Access VBA course

Countif

How do you count if for more than one if? Count if row1 = 1 AND row2<30?

RE: countif

Hi Angelo, thanks for your query. The trick is to add up the two separate COUNTIF results. In this formula, the final result is the Sum of two COUNTIF formulas:

=(COUNTIF(A1:A16, ">15"))+(COUNTIF(B1:B16, ">15"))

Alternatively,

=Sum((COUNTIF(A1:A16, ">15")), (COUNTIF(B1:B16, ">15")))

Hope this helps,

Anthony

RE: countif

Suppose I have:
=(COUNTIF(A1:A16, ">15"))+(COUNTIF(B1:B16, ">15"))

How can this be generalised to count regardless of what number I choose for 15 without going into the code? In other words, How do I do the following:

=(COUNTIF(A1:A16, >C1))+(COUNTIF(B1:B16, >C2))

where C1 is any number I choose?

RE: countif

I think the solution you provided equates to counting if it satisfied one condition OR another condition. I need it to only count if it satisfies BOTH conditions.

This is what I have so far. I've relabeled the terms to what I'm actually doing:

Names Box:
Database=A1:B30
Disease=A1:A30
Age=B1:B30

Cells:
C1=0
C2=30
D1=COUNTIF(Disease,"="&C1)

but I need something that will take into account their age, something like:

=COUNTIF(Database,Disease=C1&Age<C2)

But that doesn't work...

RE: countif

Hi Angelo, sorry for the delay in replying. You need to put in, say, cell E1 the text >15 (without any quotes) and then reference cell E1 in your COUNTIF statements instead of putting ">15" as your criteria. Try that out and see how you get on.

Anthony

RE: countif

This is what I have so far. I've relabeled the terms to what I'm actually doing:

Names Box:
Database=A1:B30
Disease=A1:A30
Age=B1:B30

Cells:
C1="0"
C2="30"

So I have a column of peoples age (A1:A30) and a column of 1s and 0s for whether or not they have a disease.
I need to count how many people are under the age of 30 AND have the disease. Also, I need the function to refer to other cells that I can easily change the criteria from "under 30" to "under" any number I want. Or to change having the disease (1) to not having the disease (0). So the final function should look something like this:

D1=COUNTIF(Database,Disease=C1&Age<C2)

but this does not work. Any suggestions?

RE: countif

Hi Angelo, soryr for the delay in replaying - very busy training at the moment. With regards to your final function I'm a bit worried about the following:

Disease=C1&Age<C2

You appear to be trying to concatenate two separate functions here. Try using AND(Disease=C1, Age<C2) instead of the ampersand and put that into the COUNTIF statement. Let me know how you get on and whether you need any further assistance.

Anthony

Tue 23 Mar 2010: Automatically marked as resolved.


 

Excel tip:

Closing Multiple Workbooks quickly

When you have several workbooks open in Excel and want to just close them all at once:

1) Hold down the SHIFT key before selecting the File menu.

2) Once in File menu release SHIFT key and select Close All option.

3) All your files will close. If files require saving Excel will ask if you want to save the changes.

View all Excel hints and tips


Server loaded in 0.13 secs.