Count rows based on multiple criteria

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

T. Valko

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))
 
G

Glenn

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Dcount (multiple criteria) 3
Count Distinct Values based on criteria 4
Counting only displayed cells in the column 3
Counting number of rows based on mutiple criteria 2
Counting multiple criteria across columns and rows??? 1
Count rows based on multiple criteria 5
need help on either doing a loop and counting specific values orusing subtotal function 2
complex sort search and replace macro 2

Top