# COUNTIF Problem

#### shane72

##### New Member
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
=SUMPRODUCT((A:A<>"")*(B:B="yes"))

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

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.

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.

Hi.

Maybe like this:
Excel Workbook
ABC
1123yes912
2456
3789yes
4
5
Sheet

Sorry, error ...
Excel Workbook
ABC
1123yes456
2456
3789yes
Sheet

Replies
9
Views
372
Replies
9
Views
309
Replies
2
Views
237
Replies
8
Views
98
Replies
1
Views
177

1,203,387
Messages
6,055,122
Members
444,763
Latest member
Jaapaap

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back