I'm relatively new to using SUMPRODUCT and haven't ever had to troubleshoot the formula when it isn't working. I've got the following construct:
Month: Numbers 1-12
Action: text list of 14 different actions taken (e.g., new, modify, write-off
Type: text list of 5 difference types (e.g., grant, loan, equity gap)
Amount: number (representing the award)
I have 600 rows of data. The four columns are named as listed above. My formula is as follows:
=sumproduct((month=9)*(action="new")*(type="grant")*amount))
Where the red 9 is above I really want to be able to refer to a list where each row represents a different month but that was proving more difficult so I'd be happy with getting the formula to work once then I'd tackle that problem.
Are the text = references what are causing my problem?
I've evaluated each piece of the sumproduct for my entire data table and I should get 6 detail items returning a total of $300,000.
Month: Numbers 1-12
Action: text list of 14 different actions taken (e.g., new, modify, write-off
Type: text list of 5 difference types (e.g., grant, loan, equity gap)
Amount: number (representing the award)
I have 600 rows of data. The four columns are named as listed above. My formula is as follows:
=sumproduct((month=9)*(action="new")*(type="grant")*amount))
Where the red 9 is above I really want to be able to refer to a list where each row represents a different month but that was proving more difficult so I'd be happy with getting the formula to work once then I'd tackle that problem.
Are the text = references what are causing my problem?
I've evaluated each piece of the sumproduct for my entire data table and I should get 6 detail items returning a total of $300,000.
