Count rows based on multiple criteria
 

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.
--
DMG
IT Professional

Count rows based on multiple criteria
 

Search for SUMPRODUCT or SUMIFS if you use XL-2007


"dmg" wrote:

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.
--
DMG
IT Professional

Count rows based on multiple criteria
 

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

--
Biff
Microsoft Excel MVP


"dmg" wrote in message
...
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.
--
DMG
IT Professional

Count rows based on multiple criteria
 

dmg wrote:
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 this:

http://www.contextures.com/xlFunctio...tml#SumProduct