Nesting countif?

**cheesysocks** · 08-17-2008, 04:50 AM

Hi all.

First time posting here so be gentle! I have a new job a part of which is where I have to provide reports using data from excel 2003 sheets that are sent to me by someone else. I add my own formulas to get the data I need. One such bit of data is...

How many rows have "yes" in col E and "w" in range M:V?

Please Login or Register  to view this content.

I can do countif's for each individual query, ie

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

but how to pair the two together?

I have googled "nesting countif" and searched here, but I haven't understood the answers I have found. Doh!

I am a newbie to Excel and I did an internal 'basic' course last week. Can anyone help in plain English before I go back to work tommorrow please?

Many thanks.

**olasa** · 08-17-2008, 05:44 AM

I suppose you basically want to say COUNTIF( col E=Y AND col M:V="W") .

In that case
=SUMPRODUCT((E2:E9="Y")*(M2:V9="W"))
It summerizes the number of rows that fullfill the criteria.

HTH
Ola

**cheesysocks** · 08-17-2008, 06:34 AM

Originally Posted by olasa

I suppose you basically want to say COUNTIF( col E=Y AND col M:V="W") .

In that case
=SUMPRODUCT((E2:E9="Y")*(M2:V9="W"))
It summerizes the number of rows that fullfill the criteria.

HTH
Ola

"Multiplies corresponding components in the given arrays, and returns the sum of those products.

SUMPRODUCT(array1,array2,array3, ...)"

Thanks, that does work well. I just wish I **understood** how it works. Lets see... BODMAS.

(E2:E9="Y")

"SUMPRODUCT treats array entries that are not numeric as if they were zeros."

(E2:E9=0) = true (I assume)

(M2:V9="W") = true

true * true? How can true be multiplied by true? I just do not understand this function. Oh well, off to Google! And thanks for your help. Attached is one sheet of the (sanatised) real data. I add columns M> ans rows 404> after I recieve the data.