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 ?

=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.

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