Adding Multiple Columns using multiple criteria

[rajkenya1]

Hi

I need some help on a formula in cell E17 of the Summary Tab of the attached spreadsheet which will sum all the amounts for the 6 months for ‘Short Term Care’ for the ‘Metro’ area for the year FY 2023 by looking up all the values and descriptions in the ‘Output-Detailed Costs. Similarly for FY 2024.

For example in the attached spreadsheet in cell E17 of the summary tab I have a simple formula for which adds all the staff development costs for FY 2023 for Short Term Care in the Metro region.

Is there any way one can do a formula which doesn’t require so much manual calculations?

 

[p45cal]

I started trying to put something together and got some formulae to give the same answers as your expected results, but when I copied across and down they stopped working because of the awful arrangement of your source data. These formulae would be very long and complicated and be impossible to maintain/adjust to new ranges.
It was easier to use Power Query to manipulate the data and produce a pivot table from that manipulated data. See the pivot table at cell C39 of the Summary sheet.
(Instead of using your original Outputs-Detailed Costs sheet I copied it and used that instead; there's a named range RawData)

 

[AlanSidman]

crossposted: https://www.excelforum.com/excel-ge...umns-using-multiple-criteria.html#post5700813

 

[vletm]

rajkenya1
As You have read from Forum Rules - please reread those next

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

Ps All Forums have same kind of rules with Cross-Posting.

 

[rajkenya1]

Hi

Thank you for letting me know about this.

rajkenya1
As You have read from Forum Rules - please reread those next

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

Ps All Forums have same kind of rules with Cross-Posting.

T

 

[rajkenya1]

Thank you for trying to help me. I will look into it. Thanks so much

I started trying to put something together and got some formulae to give the same answers as your expected results, but when I copied across and down they stopped working because of the awful arrangement of your source data. These formulae would be very long and complicated and be impossible to maintain/adjust to new ranges.
It was easier to use Power Query to manipulate the data and produce a pivot table from that manipulated data. See the pivot table at cell C39 of the Summary sheet.
(Instead of using your original Outputs-Detailed Costs sheet I copied it and used that instead; there's a named range RawData)