SumIf vs Match

[gheyman]

I have a formula that looks down column B to find a match and then adds the values across its row and sums its values by Years.

I have a new requirement that is sort of the same. In the above when it looks down column B it stops there and adds the years. If the value it searches for in B is listed two or more times, it doesnt add all the values. It stops at a Match.

My new situation needs it to add evrytime it finds the match down column B more like a SumIf than a Match formula.

Is this possible, or should I give up trying to alter this formula and find another way to get to my solution. I can find other ways but they are not very efficient. I am creating new tables and them doing a sumif........

Any help or suggestions are appreciated!



=IF(ISERROR(SUMPRODUCT(--(YEAR('Estimated Hrs & ODC'!$F$3:$FA$3)=H$7),INDEX('Estimated Hrs & ODC'!$F$6:$FA$1036,MATCH(CONCATENATE($C$5," ",$B10),'Estimated Hrs & ODC'!$B$6:$B$1036,0),0))),0,SUMPRODUCT(--(YEAR('Estimated Hrs & ODC'!$F$3:$FA$3)=H$7),INDEX('Estimated Hrs & ODC'!$F$6:$FA$1036,MATCH(CONCATENATE($C$5," ",$B10),'Estimated Hrs & ODC'!$B$6:$B$1036,0),0)))

 

[T. Valko]

I have a formula that looks down column B to find a match and then adds the values across its row and sums its values by Years.

I have a new requirement that is sort of the same. In the above when it looks down column B it stops there and adds the years. If the value it searches for in B is listed two or more times, it doesnt add all the values. It stops at a Match.

My new situation needs it to add evrytime it finds the match down column B more like a SumIf than a Match formula.

Is this possible, or should I give up trying to alter this formula and find another way to get to my solution. I can find other ways but they are not very efficient. I am creating new tables and them doing a sumif........

Any help or suggestions are appreciated!



=IF(ISERROR(SUMPRODUCT(--(YEAR('Estimated Hrs & ODC'!$F$3:$FA$3)=H$7),INDEX('Estimated Hrs & ODC'!$F$6:$FA$1036,MATCH(CONCATENATE($C$5," ",$B10),'Estimated Hrs & ODC'!$B$6:$B$1036,0),0))),0,SUMPRODUCT(--(YEAR('Estimated Hrs & ODC'!$F$3:$FA$3)=H$7),INDEX('Estimated Hrs & ODC'!$F$6:$FA$1036,MATCH(CONCATENATE($C$5," ",$B10),'Estimated Hrs & ODC'!$B$6:$B$1036,0),0)))

Something like this should work. I'm going to use a generic sheet name so it'll be easier to read!

=SUMPRODUCT((Sheet!B6:B1036=C5&" "&B10)*(YEAR(Sheet!F3:FA3)=H7),Sheet!F6:FA1036)

 

[gheyman]

Perfect! Thank you VERY much!!!

 

[T. Valko]

Perfect! Thank you VERY much!!!

You're welcome. Thanks for the feedback!