Excel formula to get sum in another sheet from different sheets

Question

I'm trying to get the sum of values from different sheets to Overview sheet. I've applied the index match with sum but the values are duplicate in other sheets. please help to get the sum in summary sheet.

Please let me know how can I upload the sheet to get the formula.

=SUM(IFERROR(INDEX(ZA!$D$10:$S$21,MATCH(Overview!$C16,ZA!$D$10:$D$21,0),MATCH(Overview!D$15,ZA!$D$10:$S$10,0)),0),IFERROR(INDEX(WA!$D$10:$S$21,MATCH(Overview!$C16,WA!$D$10:$D$21,0),MATCH(Overview!D$15,WA!$D$10:$S$10,0)),0),IFERROR(INDEX(EA!$D$10:$S$21,MATCH(Overview!$C16,EA!$D$10:$D$21,0),MATCH(Overview!D$15,EA!$D$10:$S$10,0)),0),IFERROR(INDEX(ME!$D$10:$S$19,MATCH(Overview!$C16,ME!$D$10:$D$19,0),MATCH(Overview!D$15,ME!$D$10:$S$10,0)),0),IFERROR(INDEX(SADC!$D$10:$S$17,MATCH(Overview!$C16,SADC!$D$10:$D$17,0),MATCH(Overview!D$15,SADC!$D$10:$S$10,0)),0))

Use a tool like this to include some markdown sample data, also include expected result etc. See How to Ask a question with a minimal reproducible example and update your question — JvdV
Hi Mish, please check my answer, is this what you expected. Else please sample snaps to give exact answer. — Regiz

Regiz Regiz · Accepted Answer · 2019-07-10T10:08:07

There is no sample snap's in your question, however the answer is my own guess based on reading your complete question.

Please share sample snap's, if need anymore clarification on this.

You can use SUMPRODUCT() function in this scenario to get exact result including duplicate values.

Below are the sample snap's as i guessed.

Sample RAW Data Created

Overview Formula Worked.

As per you formula Answer = 250

Formula =SUM(SUMPRODUCT((ZA!$D$11:$D$21=$C16)*(ZA!$E$10:$S$10=D$15)*(ZA!$E$11:$S$21>0),ZA!$E$11:$S$21),SUMPRODUCT((WA!$D$11:$D$21=$C16)*(WA!$E$10:$S$10=D$15)*(WA!$E$11:$S$21>0),WA!$E$11:$S$21),SUMPRODUCT((EA!$D$11:$D$21=$C16)*(EA!$E$10:$S$10=D$15)*(EA!$E$11:$S$21>0),EA!$E$11:$S$21),SUMPRODUCT((ME!$D$11:$D$21=$C16)*(ME!$E$10:$S$10=D$15)*(ME!$E$11:$S$21>0),ME!$E$11:$S$21),SUMPRODUCT((SADC!$D$11:$D$21=$C16)*(SADC!$E$10:$S$10=D$15)*(SADC!$E$11:$S$21>0),SADC!$E$11:$S$21))

New Formula Answer = 400

Excel formula to get sum in another sheet from different sheets

1 Answers