I'm trying to figure out a way to sum a row based on two cells in the same row. I tried to use a sumproduct but I'm getting an error as I have text in my sum range. Any way to ignore the text to just sum the numbers? Maybe sumproduct isn't even the best way to go about this? I'm trying to get the top section to work but finding the month, the total for each section, and sum the corresponding row. Thanks!
https://docs.google.com/spreadsheets/d/1LnT2iYA7UCkQeB57ow2IASQkhcpyVxO917Chh2M3YuQ/edit#gid=0
=sumproduct(($C$9:$E$56)*($A$9:$A$56=C$1)*($B$9:$B$56=$B2))
I expected that formula to sum the row being referenced by the corresponding month and staff total line.
You can simply add an IFERROR inside the SUMPRODUCT
=SUMPRODUCT(IFERROR($C$9:$E$56*($A$9:$A$56=C$1)*($B$9:$B$56=$B2)))
You could also do it with a single formula in C2 using the MAP function (see tab 'z' for a demo):
=ARRAYFORMULA(MAP(C1:E1&B2:B4,LAMBDA(x,SUM(IFERROR(C9:E56*(A9:A56&B9:B56=x))))))