COUNTIF AND

[Peter100]

I want to count two ranges a15:a74 and g15:g74 as long as the first range contains "YES" and the second range contains "YES"

can one combine multiple countif statements ?

 

[Yogi Anand]

Hi Peter100:

You can use the following SUMPRODUCT formula

=SUMPRODUCT((A15:A74="Yes")*(G15:G74="Yes"))

Regards!

Yogi

 

[Peter100]

Hi Yogi

I assume this has to be entered as an array ?

 

[Aladin Akyurek]

[...]
I assume this has to be entered as an array ?

No. See

http://www.mrexcel.com/wwwboard/messages/8961.html

for more.

 

[Peter100]

Hi Again

Aladins formula is great, but it appears to include any blank cells in the range
The actual criteria I'm evaluating is either
"LEFT" & 0

where I want to do acount of the range that includes zeros but not if the other range has a corresponding "LEFT"

 

[Andrew Poulsom]

You can use ISNUMBER to exclude blank cells, like this:

=SUMPRODUCT((A15:A74=0)*ISNUMBER(A15:A74)*(G15:G74<>"LEFT"))

 

[Aladin Akyurek]

On 2002-10-21 03:29, Peter100 wrote:
Hi Again

Aladins formula is great, but it appears to include any blank cells in the range
The actual criteria I'm evaluating is either
"LEFT" & 0

where I want to do acount of the range that includes zeros but not if the other range has a corresponding "LEFT"

Peter,

I'm a bit lost...

Are you saying that you want to count the co-occurrences of a real 0 in one range and anything else but the word LEFT in other range?

 

[Peter100]

Hi Aladin

yes

 

[Aladin Akyurek]

On 2002-10-21 05:19, Peter100 wrote:
Hi Aladin

yes

=SUMPRODUCT((A1:A6=0)*(B1:B6<>"LEFT"))

Adjust the ranges to suit.

 

[Andrew Poulsom]

Does that allow for blank cells in the range A1:A6?

See my posting.