The Problem: I need to count all the rows based on 2 or more criteria
Bright Light Shines: Hey, this COUNTIF formula looks like it may workà
The power company: Shuts off that bright light because of the multiple conditionsà
The Solution: SUMPRODUCTà
A B C 1 Dogs New 2 Dogs New 3 Dogs Old 4 Cats New 5 Cats Old 6 Mice Old 7 Mice Old
My criteria is to count how many new dogs we have:
gives me an answer of 2à (but how you say)
OK, lets take the table and show it in a way that the formula sees it:
A B C 1 1 1 2 1 1 3 1 0 4 0 1 5 0 0 6 0 0 7 0 0
The first part of the formula (A1:A7=öDogsö) looks at the area A1 through A7 for the string ôDogsö. Where it finds it, it will indicate a TRUE (which is represented with the number 1) for that cell. Where it does not find it, it will indicate a FALSE (which is represented with the number 0) for that cell.
The second part of the formula (B1:B7=öNewö) looks at the area B1 through B7 for the string ôNewö and does the same for when it finds it or not.
Once it has done this, the PRODUCT part of the function (A1:A7=öDogsö)*(B1:B7=öNewö) is where we tell it to multiply these results together which will look like: