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)

 

[Kmitchell]

I'm trying to sum up $F$7:$B$124 it matches $c$6 and $H18. Does that make sense?

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

 

[Aladin Akyurek]

I'm trying to sum up $F$7:$B$124 it matches $c$6 and $H18. Does that make sense?...

Which subrange exactly matching C6 and H18 would delıver?

 

[Kmitchell]

F7:bk7

 

[Kmitchell]

each row should sum up, would like for it to sum up every row just in case column H isn't unique

 

[Aladin Akyurek]

I'm trying to sum up $F$7:$B$124 it matches $c$6 and $H18. Does that make sense?

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

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)

each row should sum up, would like for it to sum up every row just in case column H isn't unique

Still not crystal clear. Here a guess nevertheless...

Assuming that the formula in some cell of the SUMMARY sheet,

Control+shift+enter, not just enter:

=SUM(IF('SCHEDULE USE'!$F$3:$BK$3=C$6,IF('SCHEDULE USE'!$BO$7:$BO$124=$H18,'SCHEDULE USE'!$F$7:$BK$124)))

In case you have error values in the range to sum like #N/A...

Control+shift+enter, not just enter:

=SUM(IF('SCHEDULE USE'!$F$3:$BK$3=C$6,IF('SCHEDULE USE'!$BO$7:$BO$124=$H18,IF(ISNUMBER('SCHEDULE USE'!$F$7:$BK$124),'SCHEDULE USE'!$F$7:$BK$124))))

 

[Kmitchell]

Thank you for your reply. I think the file I am working off is corrupt because these formulas should work and they don't. I've tested them on other files. Now the trick is figuring out the best way to move the data to a different file.

 

[Kmitchell]

Actually I take that back both formulas worked! Do you know why did these work and the sumproduct did not?

 

[Aladin Akyurek]

Actually I take that back both formulas worked! Do you know why did these work and the sumproduct did not?

The formula I suggested disregards the errors like #N/A, etc. which might occur in the multi-column range that we want to sum.

 

[Kmitchell]

Thank you so much! I was struggling!

 

[Aladin Akyurek]

Thank you so much! I was struggling!

You are welcome.