SUMIFs and AVERAGEIFs for LARGE N with multiple criteria & range

Hi,

quick and simple question I have a data set with products, prices, product categories, revenues and quantities sold. I want to sum (or average, but let's stay with sum for this question) the quantities based on top 5 revenue-generating products based on category, and a price range (0,8*Price and 1,2*Price).

Thank you!

Julian

Hi,

a very quick attempt: a sample file would be helpful.

In column C product categories

In column D revenues

I use an helper column G to get a list of sums of revenues per category

=IF(COUNTIF($C$2:C2,C2)=1,SUMIF(C:C,C2,D:D),"")


The formula (AGGREGATE available from Excel 2010)

=SUMPRODUCT(AGGREGATE(14,6,G1:G1000/((B1:B1000>=0,8)*(B1:B1000<=1,2)),ROW(1:5)))

sums first 5 top categories per revenue.

Pivot table probably are the best resource to achieve this output.

Hope it's a little help

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Hi. Use this array formula:


=SUM(LARGE(IF($D$5:$D$66=$H$5,IF($C$5:$C$66>=$J$5,IF($C$5:$C$66<=$K$5,$F$5:$F$66)),0),{1,2,3,4,5}))


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Hi All,

not sure

I'm using an helper column reading quantities

In G5

=IF(COUNTIFS($B$5:B5,B5,$D$5:D5,D5)=1,SUMIFS(F:F,B:B,B5,D:D,D5,C:C,">="&$J$5,C:C,"<="&$I$5),0)

In M5 first 5 results:


=SUMPRODUCT(LARGE((D5:D66=H5)*(C5:C66<=I5)*(C5:C66>=J5)*G5:G66,ROW(1:5)))

Maybe I'm misunderstanding your needs.

Please refer to the attachment.

Regards

Is this correct?? (In the absence of an expected answer it's a bit of a guess...) the yellow cell.

=SUM(LARGE(IF($D$5:$D$66=$H$5,IF($C$5:$C$66>=$J$5,IF($C$5:$C$66<=$K$5,$E$5:$E$66)),0),{1,2,3,4,5}))

array entered, as previously

We're not there yet. Maybe it's me being thick... but.

In example long, I have filtered by Category A. The five values that make your expected result are not all eligible as two (red) falls outside the acceptable price range (row 45 & 53). Can you have another go???

Edit: I should have added: I am convinced that this formula (array) is correct:

=SUM(LARGE(IF($D$8:$D$69=$B$5,IF($C$8:$C$69>=$D$5,IF($C$8:$C$69<=$E$5,$F$8:$F$69)),0),{1,2,3,4,5}))

which (with a few changes to the range) was posted by me back at Post #5????!!!

In the sample post#9 you show anticipated results cell H5 of =F8+F27+F45+F51+F53

But C45 and C53 are < 64. They are 32.00 € and 36.00 €.

Would you explain why they are included in H5

This is the only way I can come up with your proposed figure, and I have difficulty believing it is correct given the instructions.

=SUMPRODUCT(MMULT(--(LARGE(($D$8:$D$69=$B$5)*($C$8:$C$69<=$E$5)*$E$8:$E$69,{1,2,3,4,5})=$E$8:$E$69),{1;1;1;1;1}),$F$8:$F$69)

Also this array entered.

=SUM(IF(LARGE(($D$8:$D$69=$B$5)*($C$8:$C$69<=$E$5)*$E$8:$E$69,{1,2,3,4,5})=$E$8:$E$69,$F$8:$F$69))

Originally Posted by FlameRetired

In the sample post#9 you show anticipated results cell H5 of =F8+F27+F45+F51+F53

But C45 and C53 are < 64. They are 32.00 € and 36.00 €.

Exactly my point frpom Post # 10...

@ Glenn, oops! My bad. I missed that part.

The only way I could get it to return 1024 was if I removed the $C$8:$C$69>=$D$5 criteria and apply LARGE to the Total sales.

Neither change fits the instructions.