SUMIF INDEX Help

[fklioside]

I am trying to sum the below based on how many months. I normally use a Sum INDEX but trying to build it into a SUMIF instead. My ideal result should be 9 but I keep getting 3. Is there a better way to do this? Any help would be greatly appreciated!

 

[pgc01]

Hi

You can check the help for SumIf(). The criteria range will be resized to the same size of the sum range.

You can, however, use SumProduct() instead, like (not tested):

=SUMPRODUCT((D10:D15=J10)*E10:INDEX(E10:G15,,J11))

 

[fklioside]

Hi

You can check the help for SumIf(). The criteria range will be resized to the same size of the sum range.

You can, however, use SumProduct() instead, like (not tested):

=SUMPRODUCT((D10:D15=J10)*E10:INDEX(E10:G15,,J11))

Thank you this works! Curious can I replace D10:D15 and E10:G15 with named ranges? Or will not that work since the *E10 portion?

 

[pgc01]

Hi

No problem. Define the data range of the table for ex. tblData referring to $E$10:$G$15 and use:

=SUMPRODUCT((D10:D15=J10)*INDEX(tblData,,1):INDEX(tblData,,J11))