SUM WITH INDEX MATCH ERROR

If I understand the goal correctly, you might use:

=SUM(IF((month = C13) * (Date = C14) * (Branch = C15) * (item = C16), val, 0))

The attached workbook uses conditional formatting to highlight the values meeting the criteria so you can verify results.

=SUM(MMULT(TRANSPOSE((A5:A9=C15)*(B5:B9=C16)),TRANSPOSE(MMULT((C1:N1=C13)*(C2:N2=C14),TRANSPOSE(C5:N9)))))

This formula works in my sample file and in modern and legacy Excel. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024.

Hi Rio01​ 

For simpler way, you can use SUMPRODUCT as shown below:

=SUMPRODUCT(($A$5:$A$9=$S$7)*($B$5:$B$9=$S$8)*($C$1:$N$1=$S$5)*($C$2:$N$2=$S$6)*$C$5:$N$9)

How this works:

• $A$5:$A$9=$S$7 → matches Branch
• $B$5:$B$9=$S$8 → matches Item
• $C$1:$N$1=$S$5 → matches Month
• $C$2:$N$2=$S$6 → matches Date

Multiplying them ensures only the cells that satisfy all conditions are picked.

SUMPRODUCT then adds up all those values.

Thank you, Harun24HR​ for the sample file. It really helped to use the data for trying the formulae.

You need nested FILTER() then SUM(). Try-

=SUM(FILTER(FILTER(C5:N9,(C1:N1=C13)*(C2:N2=C14),0),(A5:A9=C15)*(B5:B9=C16),0))

Download the sample file.

Hi Mathetes,

Thank you for asking, you can access the case on this file https://bdfgrp-my.sharepoint.com/:x:/r/personal/muhammadreza_nugraha_external_beiersdorf_com/Documents/Documents/TES%20DATA%20STOCK.xlsx?d=w0501dd1caaf04e45950339e0127abf03&csf=1&web=1&e=Dzcs6S

My goal is trying to sum and match every single date on formatted summary with other criterias since on raw data tab the dates are not sequential, as additional i want to minimize the effort to match the different column date on raw data by selecting all column and row to be sum (so i dont need to change manually the column later)

can you help me to check on this formula or there's any other alternative to pull this data?

Could you try again to explain what you're trying to do? And better than an image, if you could attach the actual file (or provide a link to OneDrive or some other repository), we could provide a working example of whatever solution.

But it's not clear from your description what you want the sum to be in cell D19, and what the criteria are, or how you want to be able to change things dynamically. So give a few examples of what you mean.