What is the formula to get data for selected items from a large database

[Patthar]

Summary report for the following data:

15 Depots, 3 business units and about 900,000 rows of data for costs

On a separate sheet, I want to be able to look at total cost of each cost category by Depot and by Business Unit

I can do a pivot report. But want to see a summary where the user can choose the depot by way of checkbox and the costs, only for the depot or depots checked, for each cost category are shown by business unit. I wonder what formula will work here?


Depot Business Unit Cost 1 Cost 2 Cost 3
AAA 111
112
113
BBB 111
112
113


File attached (Excel 365)

[FlameRetired]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Patthar]

Thanks - how do I edit the title?

[Patthar]

Done. Thanks

[FlameRetired]

Please try in J12 filled down and across to L17.

Please Login or Register  to view this content.

[Patthar]

Thanks very much.

Is there any way the user can select depot / depots which are listed in Column A, by way of a tick in a tick-box, and the result is summarised in Column G, for each Depot ticked or the number of depots ticked.

[FlameRetired]

You will need to upload a sample file that is an exact example of what you are attempting to describe. What you have uploaded shows the user has already selected 'Depots' in column H. Selecting by "tick boxes" makes no sense. The 'Depots' are already selected.

Would the use of in-cell drop downs in column H (and possibly I?) be of any use? See those in the attached along with the formula proposed in post #5 above.

[Patthar]

Thanks.

I have attached the file.


= In Cell S9, total of cost 1 from from column D for the depot and business unit ticked in column J and column n
= In Cell S10 total of cost 2 from from column D for the depot and business unit ticked in column J and column n
= In cell S11, total of cost 3 from from column D for the depot and business unit ticked in column J and column n

If more than one depots are ticked, need the total of all the depots ticked

Hopefully, I have explained well.

[FlameRetired]

In the attached I put random values. Otherwise all results would be 0.

Please Login or Register  to view this content.

[Patthar]

Thanks.

Something is not right.

When I tick AAA in column J, and tick Business Unit 111 in column N, the total for cost 1 in cell S9 is 116, which is not correct - it should be 47. also the amounts in cell S10 and S11 are not correct

[JeteMc]

Try this three formula* modification of Dave's formula:
For S9: =SUMPRODUCT((D5:D44)*ISNUMBER(MATCH($B$5:$B$44,IF($K$9:$K$23,$I$9:$I$23),0)*MATCH($C$5:$C$44,IF($O$9:$O$11,$M$9:$M$11),0)))
For S10: =SUMPRODUCT((E5:E44)*ISNUMBER(MATCH($B$5:$B$44,IF($K$9:$K$23,$I$9:$I$23),0)*MATCH($C$5:$C$44,IF($O$9:$O$11,$M$9:$M$11),0)))
For S11: =SUMPRODUCT((F5:F44)*ISNUMBER(MATCH($B$5:$B$44,IF($K$9:$K$23,$I$9:$I$23),0)*MATCH($C$5:$C$44,IF($O$9:$O$11,$M$9:$M$11),0)))
*Denotes array entered formulas which are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
Note that you will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

[FlameRetired]

Yep my formula was incorrect. I haven't tried JeteMc's yet. I reworked mine. In S9:S11 try this.

Please Login or Register  to view this content.

I don't think it will require Ctrl + Shift + Enter. If it returns errors or incorrect answers then try CSE. It didn't at my end, but I have recently joined the 'Insider' group. Lately things have behaved differently in unexpected ways.

Late Edit: Now that I've looked the only difference between JeteMc's and mine is the dynamic column D:F selection.

[Patthar]

Thanks for your help.

Both formulae do not work, yet

In column J, I have selected Depots AAA and BBB and in column N, I have selected Business Unit 111. The answer in column S, shows total only for Depot AAA and not for both AAA and BBB.

I have attached the file with formula from Jetemc,

I have also tried Flameretired’s formula. It gives the same result as Jetemc’s formula, when entered as an “Array” formula – does not work without an array formula.

[JeteMc]

There are no records where Depot BBB is in the same row as Business unit 111.
When AAA, BBB and 111 are selected Dave's array entered formula (used in file attached to post #13) yields 47, 21 and 48 which are correct.
Check by filtering file.
Let us know if you have any questions.

[menem]

If you don't mind to use a helper column.

Please try this , at helper column G5

Please Login or Register  to view this content.

Then copy down until the end of data.

And
S9

Please Login or Register  to view this content.

S10

Please Login or Register  to view this content.

S11

Please Login or Register  to view this content.

Regards.

[Patthar]

Apologies for the oversight - all working well. Thanks for your help - appreciate it.

[Patthar]

If you don't mind to use a helper column.

Please try this , at helper column G5

Please Login or Register  to view this content.

Then copy down until the end of data.

And
S9

Please Login or Register  to view this content.

S10

Please Login or Register  to view this content.

S11

Please Login or Register  to view this content.

Regards.

Thanks - but the formulae provided by Flameretired and Jetemc is working.

[JeteMc]

Re: Post #16. You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.