Hi. I am trying to use the sumproduct formula, but I need to apply it to about 300 cells and each cell has different criteria. I don't want to have to go cell by cell to put each criterion in and am wondering if there is a faster/better way to do this? To explain this further in column A I have a list of names. In column D I have a list of these same names, but they can be reoccurring names and the names can appear up to 4 different times in that list. In column E, I have a point total that goes with each name in column D. In Column B, I want to use a sumproduct formula for each cell next to the names in column A to look up and add up the points that occur with their names in Column C and D. Here is an example of what I'm talking about:
Since I have to change the name in the criteria of each SumProduct formula in column B, it's going to take a long time and be very tedious to go down a list of 300 formulas in column B and make the changes. Is there a good way to do this or am I stuck going cell by cell for each formula? Perhaps there is a better formula for me to be using? In Column A I only want each name once and I want to total all of their instances from columns C and D and then I plan to sort columns A and B from greatest to smallest. I hope I explained this so it makes sense. Thanks for your help.
| All-Time Career Totals | Score | All-Time Season Best | Score | |
| Erica | SumProduct Formula All of Erica's scores | Erica | 24 | |
| Sam | SumProduct Formula All of Sam's scores | Sam | 23 | |
| Carolyn | SumProduct Formula All of Carolyn's scores | Carolyn | 23 | |
| Liz | SumProduct Formula All of Liz's scores | Liz | 23 | |
| Kaitlyn | SumProduct Formula All of Kaitlyn's scores | Kaitlyn | 23 | |
| Erica | 20 | |||
| Sam | 19 | |||
| Sam | 19 | |||
| Liz | 19 |
Since I have to change the name in the criteria of each SumProduct formula in column B, it's going to take a long time and be very tedious to go down a list of 300 formulas in column B and make the changes. Is there a good way to do this or am I stuck going cell by cell for each formula? Perhaps there is a better formula for me to be using? In Column A I only want each name once and I want to total all of their instances from columns C and D and then I plan to sort columns A and B from greatest to smallest. I hope I explained this so it makes sense. Thanks for your help.
