sumifs(index(match vs sumproduct

[Kmitchell]

Hello - I have a formula that isn't doing what I had hoped (shown below), it's returning the first value that meets the index(match criteria but I'm looking to have it sum up all values that match row 6 and column H.


F7:BK124 sits all the data to sum up. Should I be using a sumproduct instead? Any suggestions greatly appreciated.

SUMIFS(INDEX('SCHEDULE USE'!$F$7:$BK$124,0,MATCH(SUMMARY!C$6,'SCHEDULE USE'!$F$3:$BK$3,0)),'SCHEDULE USE'!$BO$7:$BO$124,SUMMARY!$H18)

 

[Jonmo1]

Try

=SUMPRODUCT(('SCHEDULE USE'!$F$3:$BK$3=SUMMARY!C$6)*('SCHEDULE USE'!$BO$7:$BO$124=SUMMARY!$H18)*'SCHEDULE USE'!$F$7:$BK$124)

 

[Kmitchell]

Thank you for your quick reply.

I modified it to grab H7 instead of H18 but it's returning #N/A. Am I missing a parenthesis?

SUMPRODUCT(('SCHEDULE USE'!$F$3:$BK$3=SUMMARY!C$6)*('SCHEDULE USE'!$BO$8:$BO$124=SUMMARY!$H7)*'SCHEDULE USE'!$F$8:$BK$124)

 

[Jonmo1]

If there are any pre-existing #N/A errors within any of the referenced ranges
That would cause the formula to return #N/A as well.

 

[Kmitchell]

ADVERTISEMENT

Does row 3 have to have a value in every cell or can they be intentionally left blank?

 

[Jonmo1]

Blanks in Row3 won't cause any Error (they won't meet the criteria of =C6, but no error).

 

[Kmitchell]

ADVERTISEMENT

The #N/A isn't apparent to me as to the cause...there are no pre-existing #N/A errors on the source tab

 

[Jonmo1]

Your formula refers to more than 1 tab.

#N/A in ANY of the referenced cells within that formula will cause the formula to be #N/A

 

[Kmitchell]

I'll have to trouble shoot, every single row and column is returning #N/A yet when I manually grab those elements on source tab there aren't any errors

 

[Aladin Akyurek]

Hello - I have a formula that isn't doing what I had hoped (shown below), it's returning the first value that meets the index(match criteria but I'm looking to have it sum up all values that match row 6 and column H.


F7:BK124 sits all the data to sum up. Should I be using a sumproduct instead? Any suggestions greatly appreciated.

SUMIFS(INDEX('SCHEDULE USE'!$F$7:$BK$124,0,MATCH(SUMMARY!C$6,'SCHEDULE USE'!$F$3:$BK$3,0)),'SCHEDULE USE'!$BO$7:$BO$124,SUMMARY!$H18)

Could you clarify what range exactly is this formula intended to pick out and sum?