Index Match Sumif formula

[Neilesh Kumar]

Dear Experts,

Please find here attached sample workbook, i am expecting the sum numbers in Report tab from cell E3 to H12 based on Report tab criterias from Data tab table numbers. The criteria in Report tab are Department Head, Department, New/Renewal from column B, C, D and Row Cell E2:H2. I have mentioned my expected result in Report tab Cell E3.

I have explained my expected result in Report tab cell E3, request to you please do let me know if need further clarification. Thank you for your valuable support.

Regards,

Neilesh

[Pete_UK]

Please explain where your expected value of 42.97 comes from.

Pete

[63falcondude]

Try this in E3:

=SUMIFS(Data!$G:$G,Data!$D:$D,$C3,Data!$E:$E,$D3,Data!$F:$F,RIGHT(E$2))

It returns 42.97

If you want to also include department head, get rid of the merged cell (B3) and fill in all of column B separately. Then you can use this:
=SUMIFS(Data!$G:$G,Data!$C:$C,$B3Data!$D:$D,$C3,Data!$E:$E,$D3,Data!$F:$F,RIGHT(E$2))

See attachment.

[Sam Capricci]

but your total in E3 is $42.97 for A/HR/New/Q1 but when applying the filter to data tab the only way it comes back as $42.97 is if I ignore the department head.
Is that ok or does it have to take the department head into account (which would make the answer $5.00 instead)?
AND, do you absolutely need the merged cells in the Report tab column B?

[Neilesh Kumar]

Thank you so much dear experts for your valuable support.

[63falcondude]

Glad we could help.

Thanks for the rep!