Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Forget SUMIF, COUNTIF and VLOOKUP | Excel Worksheet Functions | |||
How do I set up a countif, or sumif that is multiconditional? | Excel Worksheet Functions | |||
problems with sumif and countif | Excel Discussion (Misc queries) | |||
Reference Cells with Sumif or Countif | New Users to Excel | |||
Modify SUMIF and COUNTIF to work with SUBTOTALS | Excel Worksheet Functions |