SUM all Mutiple index match results

[fdiddy]

Hi All,
I have created a formula that has multiple index match conditions to satisfy before outputting a result

=(IFERROR(INDEX(DD2!G$2:$G$5916,MATCH(1,INDEX(($D$5=DD2!$D$2:$D$5916)*($I$3=DD2!$K$2:$K$5916)*(I4=DD2!$L$2:$L$5916),0,1),0)),""))

Now i want to SUM all the values that meet all of the 3 conditions in the range.

Currently the formula only outputs the first result that meets all the 3 conditions.

Badly stuck on this - many thanks in advance.

[etaf]

so you want to addup or count the values

$D$5=DD2!$D$2:$D$5916
in the range DD2!$D$2:$D$5916 . you are matching with JUST $D$5
and then in the range DD2!$K$2:$K$5916 you are matching with JUST $I$3
so for that perhaps a COUNTIFS() OR a SUMIFS() may work
BUT then we have
I4=DD2!$L$2:$L$5916
and I4 will change as you copy the formula - so I4, I5 , I6 etc
IF I4 is supposed to be $I$4

then a SUMIFS() should work
BUT what range are we summing up ? G?

SUMIFS(DD2!G$2:$G$5916, DD2!$D$2:$D$5916, $D$5 , DD2!$K$2:$K$5916, $I$3, DD2!$L$2:$L$5916, $I$4 )

[fdiddy]

etaf,

Please note DD2! is a worksheet name

If the 3 conditions are met then the result is currently 10.

However if the conditions occur 4 times in the range the result should be 40(10 * 4(conditions are met 4 times))

The range the values are in is DD2!G$2:$G$5916

[etaf]

Please note DD2! is a worksheet name

yep, i saw that was the case in the code

so in DD2!G$2:$G$5916, you have a 10 in all the cells on the rows where -

DD2!$D$2:$D$5916 = $D$5
DD2!$K$2:$K$5916 = $I$3
DD2!$L$2:$L$5916 = $I$4

so you really want to count how many times all those 3 conditions are meet on the same ROW and then multiply it by 10

would that be the same result you are looking for

yor index code - brings back a 10 - but only once - as that is what that function is designed to do

[fdiddy]

I want to add the 4 values together.
1st time all conditions are met: Value is 10
2nd time all conditions are met: Value is 15
3rd time all conditions are met: Value is 12
4th time all conditions are met: Value is 30

Sum total is 67.

yes the formula is giving correct results but i need the total to be 67 not just 10.

I am not sure is it possible to sum multiple index match, but thats what i am trying to find out in this post.

[etaf]

doesnt the sumifs work?

as thats what i thought you meant first time

[fdiddy]

doesnt the sumifs work?

as thats what i thought you meant first time

I am not sure where to put the SUMIFS into this formula.

=(IFERROR(INDEX(DD2!G$2:$G$5916,MATCH(1,INDEX(($D$5=DD2!$D$2:$D$5916)*($I$3=DD2!$K$2:$K$5916)*(I4=DD2!$L$2:$L$5916),0,1),0)),""))

[fdiddy]

SUMIFS(DD2!G$2:$G$5916, DD2!$D$2:$D$5916, $D$5 , DD2!$K$2:$K$5916, $I$3, DD2!$L$2:$L$5916, $I$4 )

This worked perfectly - thanks for the great support !

[etaf]

glad to have helped