SumIf with use of large

[spikeyz102k]

Good morning,

I am aware that a Pivot table is probably the best option for this, but basically the field listed where the data selected is a drop down and as I cannot get the Pivot Table to check the cell in order to filter different data I feel this is the best way however I cannot get the formula to work. Suggestions & Help would be greatly appreciated.

=SUMIF('Sales Data'!J:J,'Profit Worksheet'!B2,SUM(LARGE(('Sales Data'!D:D,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}))

So basically I want the top 20 items to be selected based on the cell value in B2, so when I change the cell value or "customer name" it automatically reads up the correct top 20 as a total sum.

Hopefully what I am asking for makes sense.

Suggestions and help welcome.

Thank you.

 

[FormR]

So basically I want the top 20 items to be selected based on the cell value in B2, so when I change the cell value or "customer name" it automatically reads up the correct top 20 as a total sum.

Hi, welcome to the forum!

I think I understand and here is an option you can try. Note, this assumes you are putting the formula in the "Profit Worksheet" sheet.

=SUM(LARGE(IF('Sales Data'!J2:J1000=B2,'Sales Data'!D2:D1000),ROW(INDEX(B:B,1):INDEX(B:B,MIN(COUNTIF('Sales Data'!J2:J1000,B2),20)))))

This is an array formula, so you will need to commit it with Control+Shift+Enter.

Adjust the references to "Sales Data" rows as appropriate, but try to keep them as small as possible for efficiency reasons.

 

[spikeyz102k]

Thank you for the welcome!

Hi, welcome to the forum!

I think I understand and here is an option you can try. Note, this assumes you are putting the formula in the "Profit Worksheet" sheet.

=SUM(LARGE(IF('Sales Data'!J2:J1000=B2,'Sales Data'!D2:D1000),ROW(INDEX(B:B,1):INDEX(B:B,MIN(COUNTIF('Sales Data'!J2:J1000,B2),20)))))

This is an array formula, so you will need to commit it with Control+Shift+Enter.

Adjust the references to "Sales Data" rows as appropriate, but try to keep them as small as possible for efficiency reasons.

Thank you for your quick response, however the above does not work, even if I adjust the values. Maybe it's better if I explain the cells and the values as the above will not work.

B2 = Lookup Cell value
The rest of the table currently vlookups against a pivot table to give total / overall values by customer group type.

What I am trying to achieve (from the raw data under "sales data") is pull out the top 20 (Accounts within the group) as a sum against the Total Group associated in Cell B2.

The only other option I have currently is to do a second pivot table, setting up a "top 20" then directing that back into the table so I end up with the "Total for all Groups" and the total for the top 20, separately. However, this is a manual adjustment and was hopeful of a way in which to provide exactly the same information but via a sumif:large function formula.


It would be easier if I could just get the pivot table filter to adjust to the data I select in Cell B2 on the 'Profit Worksheet' to autofill the data & criteria depending on what drop down I select but my VBA skill is at the lower end of the scale.

To be crystal clear.

B2 = Group
The rest of the data in the current table is vlooking up against cell B2 in order to obtain the values against the group. i.e. sales/profit/costs.
Data I am looking for - Top 20 Sales accounts within that group so I can further strip down the information and data.

Worksheets
Profit Worksheet (Where all the data is being manipulated)
Sales Data Pvt
Top 20 (Pivot Table - manual adjustment - Cell to be adjusted against filter in B6 but to lookup against Profit Worksheet Cell B2)
Sales Data (Core Data)

I hope I am making sense, if not please tell me.

Thanks again.

 

[FormR]

Hi,

Try to post a small scaled down example of some actual data. So, for example 10 rows of data from column J, 10 rows of data from column D - the value of B2 and what your expected result would be for that example data if you wanted results for the top 3.