# Thread: CUMULATIVE COUNTIF...sorta

1. Registered User
Join Date
Aug 2009
Posts
2

## Unanswered: CUMULATIVE COUNTIF...sorta

I want to have a cumulative COUNTIFS. I want it to countif "criteria 1" is there and then add another one to the count if "criteria 2" is present and add another one to the count if criteria 4 is present so in the end I can get a total of 3 in the cell and not one.

Now lets say I have a category column titled Teachers and I wanted to count how many teachers where in that cell.

So CEll A3 = Language Arts Teacher
Geography Teacher
Math Teacher
my count ifs would be =COUNTIFS(A2:A4,"*" & "Language Arts Teacher" & "*",J2:J4,"School A") + =COUNTIFS(A2:Af4,"*" & "Math Teacher" & "*",J2:J4,"School B") + ....etc

What formula do I great so it can scan the cell and count the 3 teachers total ?

2. Registered User
Join Date
Aug 2009
Posts
2
=SUMPRODUCT(--(ISNUMBER(SEARCH({"Language Arts Teacher","Science Teacher","Math Teacher"},AU3))))

Let me clarify, I want it to count only if cell J2:J4 contains "MELROSE HIGH SCHOOL" otherwise keep at 0.

3. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi,

I don't have XL 2007 at work to check, but this should do it -

=SUM(COUNTIFS(A2:A4,{"*Language Arts Teacher*","*Science Teacher*","*Math Teacher*"},J2:J4,"MELROSE HIGH SCHOOL"))

Of course, these all have the word "Teacher" in common so if you want all teachers you might be able to reduce it to just "*Teacher*" ?

Hope that helps...
Last edited by Colin Legg; 12-30-09 at 08:28.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•