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?


--
Boulder257
------------------------------------------------------------------------
Boulder257's Profile: http://www.excelforum.com/member.php...o&userid=20053
View this thread: http://www.excelforum.com/showthread...hreadid=505320

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


HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=505320

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

"Boulder257" wrote:


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?


--
Boulder257
------------------------------------------------------------------------
Boulder257's Profile: http://www.excelforum.com/member.php...o&userid=20053
View this thread: http://www.excelforum.com/showthread...hreadid=505320

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"
wrote
in message
...

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?


--
Boulder257
------------------------------------------------------------------------
Boulder257's Profile:
http://www.excelforum.com/member.php...o&userid=20053
View this thread:
http://www.excelforum.com/showthread...hreadid=505320

Thanks Steve...by the way, what does HTH mean?


--
Boulder257
------------------------------------------------------------------------
Boulder257's Profile: http://www.excelforum.com/member.php...o&userid=20053
View this thread: http://www.excelforum.com/showthread...hreadid=505320

No problem.

HTH - Hope that helps.


Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=505320

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=505320

Wow! great help all. Thanks again.


--
Boulder257
------------------------------------------------------------------------
Boulder257's Profile: http://www.excelforum.com/member.php...o&userid=20053
View this thread: http://www.excelforum.com/showthread...hreadid=505320