Sum amounts from different worksheet with multiple criteria

[kenyaraj]

Hi

Thanking you in advance for your assistance.

In the attached spreadsheet, if you look at the summary tab, i want a formula in the summary tab say in say cell ref E5 which basically looks at the GL code in column C in (summary sheet) and looks up all amounts related to that code in the costs sheet if the cost centre is ICT (cell ref D2 in summary sheet) and if there is no value than it returns a blank or zero. Similarly in the summary tab is cell D2 is Finance then all the costs related to a particular GL code in the costs sheet related to finance only will appear in that cell in the summary sheet. Hope this makes sense.

Thanks so much

[Pete_UK]

You can use this formula in cell E5 of the Summary sheet:

=SUMIFS(Costs!H:H,Costs!$C:$C,TRIM($D$2),Costs!$B:$B,$C5)

then copy across and down as required.

Hope this helps.

Pete

[kenyaraj]

Dear Pete.

Thank you so much for the prompt reply and helping me with a brilliant solution to my query. Thank you so so much. Ever so grateful. Bless

[kenyaraj]

Thank you so much once again Pete for your reply and solution. You are a legend

[Pete_UK]

You're welcome - glad to help, and thanks for the rep.

You will notice that I used ...TRIM($D$2)... in the formula, and that is because you had two trailing spaces after ICT in that cell - you should try to avoid trailing spaces in your input, or you could use a data validation drop-down in that cell to make things easier.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, your profile states that you are using Excel 2003, but you wouldn't be able to use SUMIFS in that version, so please update your User CP to show your current version.

Pete

[kenyaraj]

Once again thank you so much. I appreciate your help. I will also change the CP as i am using the latest version of excel. I am not sure what training spaces mean but will have a look and amend. Thank you so so much.

[AliGW]

You have not yet updated your profile - please do so ASAP.

Trailing spaces are spaces that appear after text in a cell.