countif

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

# Countif

Resolved · Low Priority · Version 2003

Angelo has attended:
Excel VBA Intro Intermediate 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.

Training courses

Training information:

 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 What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### 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