Count rows based on multiple criteria

  • Thread starter Thread starter dmg
  • Start date Start date
D

dmg

I have a need to count the number of rows in an Excel spreadsheet based on
contents of multiple cells. The formula is on one worksheet and all data is
on a second worksheet.

IE: count all rows that have the word "Windows" in column B and the word
"Complete" in column G.

Have not been able to figure this out with common functions like count,
countif, dcount, etc.

Help appreciated.
 
Try one of these...

If you're using Excel 2007...

Use cells to hold the criteria:

A1 = Windows
A2 = Complete

Then:

=COUNTIFS(B1:B10,A1,G1:G10,A2)

This one will work in any version of Excl:

=SUMPRODUCT(--(B1:B10=A1),--(G1:G10=A2))
 
dmg said:
I have a need to count the number of rows in an Excel spreadsheet based on
contents of multiple cells. The formula is on one worksheet and all data is
on a second worksheet.

IE: count all rows that have the word "Windows" in column B and the word
"Complete" in column G.

Have not been able to figure this out with common functions like count,
countif, dcount, etc.

Help appreciated.
Click to expand...


Try this:

http://www.contextures.com/xlFunctions04.html#SumProduct
 
Back
Top