COUNTIF Problem

[shane72]

Hello all,

I have 2 columns and i'm trying to do the following:

column A - i want to count all the cells that have some kind of data in them, but only count them if there is a YES in column B of that row.

col a col b

123 yes = count this one
456 blank = dont count this one
789 yes = count this one

thanks..
shane

 

[vaskov17]

=SUMPRODUCT((A:A<>"")*(B:B="yes"))

 

[shane72]

opps i did that wrong... i want to count the ones that don't have YES in column B.

 

[tigeravatar]

Excel 2003 and lower:
=SUMPRODUCT(--(A1:A3<>""),--(B1:B3<>"Yes"))

Sumproduct calculates like an array formula, and as such using whole columns can be quite taxing on excel, so I'd recommend using an actual range as shown or you can use a dynamic named range.

For Excel 2007 and higher:
=COUNTIFS(A:A,"<>",B:B,"<>yes")

The CountIfs will do the same thing, and isn't calculated like an array formula so it should be fine to use the whole column if desired.

 

[shane72]

Sorry about this. I typed it wrong..

I'm trying to count items in column A when the cells in column B are blank, not YES.

I will have a few things in rows 1 thru 5, so i need it to start at row 6.

col a col b
123 yes = dont count
456 blank = count it
789 yes = dont count

Thanks.

 

[Matt Rogers]

Hi.

Maybe like this:

Excel Workbook
	A	B	C
1	123	yes	912
2	456
3	789	yes
4
5
Sheet

 

[Matt Rogers]

Sorry, error ...

Excel Workbook
	A	B	C
1	123	yes	456
2	456
3	789	yes
Sheet