SUMPRODUCT help

[danmarsh]

I am trying to see how many times the "Other" source either made each person reach or pass the goal. The A column specifies who has the goal. Below is the formula I came up with, but is only showing "0" when Jason should have 4 and Matt should have 1.

Please Login or Register  to view this content.

Please help!

[danmarsh]

Oh, and this should be in the code as well somehow:

Please Login or Register  to view this content.

[NBVC]

I am a little confused... why exactly do you say Matt should be 1 and Jason be 4?

[danmarsh]

Because Matt reached one of his goals because "other" was the source of the calls, not Matt.

Jason reached four of this goals because "other" was the source of the calls, not Jason.

(The highlighted rows show you which ones).

[NBVC]

Why isn't row 29 for Jason included... is it because Devon is non-zero in that row? So do it mean to count only if columns I:L are 0 and column H is >= Goal column?

[danmarsh]

Hmm, interesting question. I suppose that changes things. I would need to know how many times a goal was reached with help from any source other than the goal owner (col A).

For example, for Jason, I would need to know how many times he reached or exceeded his goals because of calls from Other, Devin, Ruben, and Matt. (Only for goals that Jason owns (Column A).

[lwflip]

I am not sure if I fully understand your question but I think this is what you want. Try this in cell I75 and drag across to L75:

Please Login or Register  to view this content.

Devin has 2 from rows 4 and 6
Ruben has 6 from rows 49, 53, 60, 61, 62 and 64
Jason has 7 from rows 25 - 27, 29, 30, 33 and 34
Matt has 4 from rows 39, 40, 42 and 47

I am assuming the data in columns H to L are who completed the goal. This checks if the owner of the goal completed 100% of the "actual" tasks and if the goal is 100% complete.

[danmarsh]

Hi lwflip, I think your formula is missing a criteria. For example, Devin should not return 2 for rows 4 and 6 because Devin had already exceeded his goal with his 12 (12>=8), so the 1 from "other" is just gravy. I am looking for the instances where the help from anyone other than Devin would have either reached or exceeded his goal. For example, on row 25, Jason did not get any calls for that goal, "Other" got two, and this made it so he reached that goal. So this should pass the sumproduct that I need.

[lwflip]

I see now. Try this instead:

Please Login or Register  to view this content.

Devin = 0
Ruben = 1 (row 64)
Jason = 5 (rows 25-27, 29, 30)
Matt = 1 (row 39)

[NBVC]

I am thinking it's a bit more complex.... I am not sure.... but this is my formula.

In I75

Please Login or Register  to view this content.

copied across.

This formula gives the sum of each row for the user, subtracts his/her values, and sees if the remainder is greater than or equal to the goal, as long as that user hasn't made the goal him/herself.

[danmarsh]

Awesome, thank you both very much! It appears that lwflip's is the formula I will go with as there was a slight difference in the results.

lwflip's formula returned a 1 for Ruben, where NBVC returned a 0. This is the only difference with the results.

Rep for both of you though, you've both helped me with this issue and also helped me understand how to process something like this.