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

need help on either doing a loop and counting specific values orusing subtotal function 2
how to count rows if 2 columns partially match 5
Trying to count based on multiple criteria & cell comparisons 4
Count columns that meet criterias 3
Hide columns based on row values 2
countifs Function not giving desired result with date criteria 2
Counting number of rows based on mutiple criteria 2
Count Distinct Values based on criteria 4

Top