98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Countif
Countif
Resolved · Low Priority · Version 2003
Mon 1 Mar 2010, 16:00Delegate Angelo said...
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?
Mon 1 Mar 2010, 16:54Trainer Anthony said...
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
Tue 2 Mar 2010, 08:32Delegate Angelo said...
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?
Wed 3 Mar 2010, 16:20Delegate Angelo said...
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...
Mon 8 Mar 2010, 14:23Trainer Anthony said...
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
Mon 8 Mar 2010, 21:15Delegate Angelo said...
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?
Tue 16 Mar 2010, 18:06Trainer Anthony said...
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.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Closing Multiple Workbooks quicklyWhen you have several workbooks open in Excel and want to just close them all at once: |
