Countif Question?!

B

Boulder257

Okay, this may be a tough one:

Let's say in Column A I have different products (1,2,3,etc)
In Column B I have whether that product has been sold ("Y" or "N")
I need to count how many of product 1 I have sold.

How can I do this without adding any additional columns?
 
G

Gareth Saunders

You will need to change your spreadhseet. You don't specify how many of
product one you have, so selecting a simple y or n to it being sold isn't
going to give you a total. You need a column with stock and one with the
amount sold and work your formula out from there

Gareth
 
C

Chip Pearson

Use the SUMPRODUCT function.

=SUMPRODUCT(--(A1:A10=1),--(B1:B10="y"))

This will count the number of items with 1 in A1:A10 and 'y' in
B1:B10.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Boulder257"
in message
news:[email protected]...
 
S

SteveG

Gareth makes a good point but based on the OP it appears that it is a
for one relationship meaning that each "Y" is considered one unit sold
so the formula works. If that is not the case then you would certainly
need to input the number of units sold in another column to calculate
the total units of product one sold correctly. If you added the number
of units sold in column C the formula would be.

=SUMPRODUCT((A1:A6=1)*(B1:B6="Y")*(C1:C6))

This will sum the number of units sold when A=1 & B=Y.



Steve
 
Top