Combining Sumproduct with an aggregate formula

Hi

I'm trying to write a function that will look for input (exercise) in column B:B. Each exercise have two corresponding rows. One for KGs and one for Reps.
When there is a match, the function should calculate a sumproduct of the two rows with KGs and Reps.

The issue is, when there are multiple matches in column B:B my formula will only calculate the sumproduct for the first match.
Is there a way to fix this?

This formula calculates the SUMPRODUCT of my rows, but only one match.

Please Login or Register  to view this content.

This formula finds the rows where the input matches up with column B:B

Please Login or Register  to view this content.

Is it possible to combine the two above formulas, or is there a better way?



Thanks in advantage

Thanks a lot HansDouwe. Your formula works great


In the data t;F4:K13 for KGs and reps, I have formulas like this on, =IF(AA99=0;"";AA99) where it return "" instead of a 0. Right now, this breaks your formula.

Is it possible to change it, so "" are ignored?


Thanks

I have added some "hidden" cells to the file.
In my own file these cells are a used to set reps and add weight increments. Based on these cells, I use the formula =IF(F5=0;"";F5) to get the reps and KGa to the array your formula did magic on

The problem is, when I have "hidden" cells with no information, this breaks the formula and it returns an #VALUE! error.

I hope it makes sense with the file.


Thanks

Thanks a lot HansDouwe. That fixed it