Google Sheets / If "complete", and name says "Name", sum up cells next to it. Filtered!

[Infiniti0606]

Hello,

First post. This is a messy one. We are using this to track renewals. Once our renewals are done on the program, we change the status to "Complete". Once in that status, I want to find a way to have each sales reps totals, added together to see their renewal rates. I was using this formula, "=SUMIF(H3:H50,"*Kendra*",I3:I50)", with the information at the bottom, but I cannot filter this. Also, there are 4 different columns where her name could show up, so I didn't know how to avoid having her name at the bottom of each column.

Does this make any sense at all?

In a perfect world, if the status cell is "Complete" than I'd like to have any instance of the reps name, be found and added to one location with their name, with all renewal stats. a "Kendra, #, #, %" for the entire sheet.

Please help.

Josh

[JeteMc]

Perhaps this will be a starting point:
1. Make a list of sales reps as in column Y
2. Apply data validation to cell H52 producing a drop down from the list
3. Populate I52:W52 using**:

Please Login or Register  to view this content.

4. For a total populate I54:J54 using**: =SUM(IFERROR(($H4:$T40=$H52)*($C4:$C40="Completed")*(I4:U40),0))
** denotes an array entered formula which I believe for google requires a preceding ARRAYFORMULA function.
5. [Alternatively] populate I54:J54 using: =SUM(I52,M52,Q52,U52)
Let us know if you have any questions.