Bookmark page
Printable version
Course updatesForum home » Delegate support and help forum » Microsoft Excel Training and help » Countif
|
|
|
Bookmark this page|
The UK's most regular 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. |
Countif
Resolved · Low Priority · Version 2003
Reply
Countif
How do you count if for more than one if? Count if row1 = 1 AND row2<30?For upcoming training course dates see: Pricing & availability
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.
|
 |
|
» Forum post: Excel 2007 |
I don't have one today!
