Forum Discussion
SUM WITH INDEX MATCH ERROR
so i want to sum all of possibility based on criteria to search value that i've adjust. the goal is to create dynamic formula that dont need to adjust per column (that's why i adjust the range formul...
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.