sumif and countif nested?
 

I need to be able to find the number of occurrence of certain type of
product sold at a certain price.

eg.
I have 2 columns.

Col A = Product Code
Col B = Selling Price

Prd Code Price
2 £1.00
9 £62.00
13 £5.00
17 £5.00
17 £5.00
17 £124.00
17 £62.00
17 £62.00
17 £5.00
18 £5.00
20 £5.00
20 £5.00
21 £62.00
21 £5.00
21 £62.00
21 £5.00
21 £5.00
21 £62.00
21 £5.00
21 £5.00
21 £62.00
21 £62.00
22 £5.00
22 £5.00
22 £5.00
22 £62.00
22 £5.00
22 £63.00
22 £5.00
22 £62.00
22 £5.00
22 £62.00
22 £5.00
22 £5.00
22 £5.00
22 £62.00
22 £5.00
24 £62.00
24 £62.00
24 £5.00

I need to be able to find how many of 22 are sold at £62, £5 and how
many of 17 are sold at £62, £5 etc.

is there an equation to solve this? I could only use countif to count
the total number of products that are sold at £62, £1,£5 etc but I
dont know how many of 22 are sold at £5, how many 17 are sold at £5.

thanks.

sumif and countif nested?
 

Let's assume A1 and B1 have text (labels) and the data is in A2:B41
To COUNT how many entries have Code of 22 and Price of 5 use this formula
=SUMPRODUCT(--(A2:A41=22),--(B2:B41=5))
You could use cell references as =SUMPRODUCT(--(A2:A41=D1),--(B2:B41=E1))
You cannot use full columns as in =SUMPRODUCT(--(A:A=22),--(B:B=5))
For more on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
ups.com...
I need to be able to find the number of occurrence of certain type of
product sold at a certain price.

eg.
I have 2 columns.

Col A = Product Code
Col B = Selling Price

Prd Code Price
2 £1.00
9 £62.00
13 £5.00
17 £5.00
17 £5.00
17 £124.00
17 £62.00
17 £62.00
17 £5.00
18 £5.00
20 £5.00
20 £5.00
21 £62.00
21 £5.00
21 £62.00
21 £5.00
21 £5.00
21 £62.00
21 £5.00
21 £5.00
21 £62.00
21 £62.00
22 £5.00
22 £5.00
22 £5.00
22 £62.00
22 £5.00
22 £63.00
22 £5.00
22 £62.00
22 £5.00
22 £62.00
22 £5.00
22 £5.00
22 £5.00
22 £62.00
22 £5.00
24 £62.00
24 £62.00
24 £5.00

I need to be able to find how many of 22 are sold at £62, £5 and how
many of 17 are sold at £62, £5 etc.

is there an equation to solve this? I could only use countif to count
the total number of products that are sold at £62, £1,£5 etc but I
dont know how many of 22 are sold at £5, how many 17 are sold at £5.

thanks.

sumif and countif nested?
 

=SUMPRODUCT(--(A1:A100=22),--(B1:B100=62))
You can have the '22' and '62' in two cells, say C1 and D1,
=SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1))
then you can get different results by changing those two cells and not have
to edit the formula,
Regards,
Alan.
wrote in message
ups.com...
I need to be able to find the number of occurrence of certain type of
product sold at a certain price.

eg.
I have 2 columns.

Col A = Product Code
Col B = Selling Price

Prd Code Price
2 £1.00
9 £62.00
13 £5.00
17 £5.00
17 £5.00
17 £124.00
17 £62.00
17 £62.00
17 £5.00
18 £5.00
20 £5.00
20 £5.00
21 £62.00
21 £5.00
21 £62.00
21 £5.00
21 £5.00
21 £62.00
21 £5.00
21 £5.00
21 £62.00
21 £62.00
22 £5.00
22 £5.00
22 £5.00
22 £62.00
22 £5.00
22 £63.00
22 £5.00
22 £62.00
22 £5.00
22 £62.00
22 £5.00
22 £5.00
22 £5.00
22 £62.00
22 £5.00
24 £62.00
24 £62.00
24 £5.00

I need to be able to find how many of 22 are sold at £62, £5 and how
many of 17 are sold at £62, £5 etc.

is there an equation to solve this? I could only use countif to count
the total number of products that are sold at £62, £1,£5 etc but I
dont know how many of 22 are sold at £5, how many 17 are sold at £5.

thanks.

sumif and countif nested?
 

Thanks guys!! its very useful! just what i want to do!

Alan wrote:

=SUMPRODUCT(--(A1:A100=22),--(B1:B100=62))
You can have the '22' and '62' in two cells, say C1 and D1,
=SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1))
then you can get different results by changing those two cells and not have
to edit the formula,
Regards,
Alan.
wrote in message
ups.com...
I need to be able to find the number of occurrence of certain type of
product sold at a certain price.

eg.
I have 2 columns.

Col A = Product Code
Col B = Selling Price

Prd Code Price
2 £1.00
9 £62.00
13 £5.00
17 £5.00
17 £5.00
17 £124.00
17 £62.00
17 £62.00
17 £5.00
18 £5.00
20 £5.00
20 £5.00
21 £62.00
21 £5.00
21 £62.00
21 £5.00
21 £5.00
21 £62.00
21 £5.00
21 £5.00
21 £62.00
21 £62.00
22 £5.00
22 £5.00
22 £5.00
22 £62.00
22 £5.00
22 £63.00
22 £5.00
22 £62.00
22 £5.00
22 £62.00
22 £5.00
22 £5.00
22 £5.00
22 £62.00
22 £5.00
24 £62.00
24 £62.00
24 £5.00

I need to be able to find how many of 22 are sold at £62, £5 and how
many of 17 are sold at £62, £5 etc.

is there an equation to solve this? I could only use countif to count
the total number of products that are sold at £62, £1,£5 etc but I
dont know how many of 22 are sold at £5, how many 17 are sold at £5.

thanks.