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 ?

 

[Peter100]

Hi

Andrew yours worked a treat

Aladin it still included the blanks

Many thanks to you all

 

[Aladin Akyurek]

On 2002-10-21 05:43, Peter100 wrote:
Hi

Andrew yours worked a treat

Aladin it still included the blanks

Many thanks to you all

Not so fast...

You mean when empty cells in both ranges...

OK, I goofed on that... However, you need to test both ranges for blanks and/or empty cells...

See...

aaMultCondCount Peter100.xls
	A	B	C	D
1	0	RIGHT
2		LEFT		2
3	0	LEFT		2
4	0	XSA
5	4	XSA
6	3	DRA
7		LEFT
8
9
10
Sheet1

 

[Peter100]

Hi Again

I see your point about testing both ranges, but now Aladins example confuses me, surely there is only one occurence of 0 and LEFT

 

[Aladin Akyurek]

On 2002-10-21 16:25, Peter100 wrote:
Hi Again

I see your point about testing both ranges, but now Aladins example confuses me, surely there is only one occurence of 0 and LEFT

Peter,

I thought we were counting co-occurrences of real 0's and non-LEFT's. There are 2 instances in the worksheet figure, colored in light yellow. Otherwise, either of:
=SUMPRODUCT((ISNUMBER(A1:A10))*(A1:A10=0)*(B1:B10="LEFT"))

=SUM(IF(ISNUMBER(A1:A10),(A1:A10=0)*(B1:B10="LEFT")))

would suffice.

Aladin
This message was edited by Aladin Akyurek on 2002-10-21 17:41

 

[Mark W.]

On 2002-10-21 00:26, Peter100 wrote:
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 ?

{=COUNT(IF(A15:A74="YES",IF(G15:G74="YES",1)))}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".