Question Involving COUNTIF and Possibly DCOUNTA

[Guest]

Hi, first time poster, long time reader here.

My question maybe quite basic so please bear with me.

What I am trying to do is to count the number of "N"s in a column that
happen to be in the same row with another column that shows a 4 or 5.

To illustrate:

N 5
Y
Y
Y
N 4
Y
N 5
Y

Y
N 1

As you can see above, both the first and second column can contain blanks.
The formula I am trying to get is to count the number of "Y"s in column 1
(already know how) and add that to the number of "N"s which correspond with a
4 or 5 in column 2 (column 2 is only filled if column 1 shows an N, and
contains one of the numbers between 1 to 5)

I hope that made sense.

Thanks in advance for your help!

 

[T. Valko]

Try this:

=COUNTIF(A1:A11,"Y")+SUMPRODUCT((A1:A11="N")*(B1:B11={4,5}))

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(A2:A10="N"),--((B2:B10=4)+(B2:B10=5)>0))


another

=SUMPRODUCT(((A2:A10="N")*(B2:B10={4,5})))

 

[PCLIVE]

One way maybe:

=SUMPRODUCT(--(A1:A11="N"),--(B1:B11=4)+(B1:B11=5))

HTH,
Paul

 

[Roger Govier]

Hi Derek

Try
=SUMPRODUCT((A1:A11="N")*(B1:B11={4,5}))

 

[Guest]

With
Col_A containing some "Y" values
AND
Col_B contains numbers ONLY when Col_A contains "N"

This formula counts "Y" values in Col_A plus cells containing 5 or 4 in Col_B:
=COUNTIF(A1:A11,"Y")+SUM(COUNTIF(B1:B11,{4,5}))

With your sample data, the formula returns 9 (6 "Y" cells + 3 cells with 5
or 4)

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Piscator]

....and yet another way
{=SUM(IF($A$1:$A$11="N",IF($B$1:$B$11=4,1,IF($B$1:$B$11=5,1,0))))}