Count If

[Guest]

I would like to Count the Sales Only that DO Not Have a Sale in B. So it
would only count the Blank sales.

A B
1 Sales
2 Supply 3
3 Sales 3
4 Supply

Total With No Sales:1

Any help would be great!!

 

[Max]

One way ..

Assume this data is in Sheet1

A B
1 Sales
2 Supply 3
3 Sales 3
4 Supply

In Sheet2
-------------
Put in A1: Sales, in A2: Supply, etc

Put in B1:
=SUMPRODUCT((Sheet1!$A$1:$A$100=TRIM(A1))*ISBLANK(Sheet1!$B$1:$B$100))
Copy down

Col B will return the number of "blank" Sales, Supply, etc from Sheet1

Adapt the ranges to suit ..
(but note that you can't use entire col references "A:A, B:B" etc in
SUMPRODUCT )

 

[Guest]

MAX I would like it to Only Count the Blank "Sales" Only.

Can this be done?

Thanks for your Help!!!!!!

 

[Frank Kabel]

Hi
but this is what his formula would do?
hardcoded alternative:
=SUMPRODUCT((A1:A100="Sales)*ISBLANK(B1:B100))

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Max]

... I would like it to Only Count the Blank "Sales" Only.

well, you could put in say cell B20 on the same sheet as the data:
=SUMPRODUCT(($A$1:$A$10="Sales")*ISBLANK($B$1:$B$10))

It's the same formula given earlier in Sheet2's B1, except that
it's now hardcoded and designed to fit your layout <g>

Adapt the ranges to suit ..

 

[Tom Ogilvy]

Typo:
=SUMPRODUCT((A1:A100="Sales)*ISBLANK(B1:B100))
should be
=SUMPRODUCT((A1:A100="Sales")*ISBLANK(B1:B100))

 

[Frank Kabel]

Hi Tom
good spot. thanks for the correction

 

[Max]

Thanks, Frank ..

... and here's a free typo correction service