Help with SUMIF using criteria in two columns - SUMPRODUCT?

[jrc999]

I have been away from heavy Excel usage for a couple of years, so I'm struggling to remember things!

I need to subtotal col E for each category in Col D, but including them only if Col B contains "CA". I thought SUMPRODUCT might be the answer, but I can't get this to work:
=SUMPRODUCT(((B2:B9)="CA")*(D2:D9=D300)*(C2:C9)) D300 contains the Category to be subtotaled, e.g. "Computer Equipment." It's returning a #VALUE error. Am I doing something wrong in the formula, or is SUMPRODUCT not the right solution?

Thanks in advance!!


[TABLE="width: 542"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD]Item[/TD]
[TD]Category[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]48 Port Switch[/TD]
[TD]Network Equipment[/TD]
[TD="align: right"]$1,479.00[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]24 Port Switch[/TD]
[TD]Network Equipment[/TD]
[TD="align: right"]$925.00[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]Cable Materials[/TD]
[TD]Network Infrastructure[/TD]
[TD="align: right"]$4,250.00[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]Cable Labor[/TD]
[TD]Network Infrastructure[/TD]
[TD="align: right"]$18,100.00[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]UniFi access points[/TD]
[TD]Network Equipment[/TD]
[TD="align: right"]$130.00[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]Warehouse Computers[/TD]
[TD]Computer Equipment[/TD]
[TD="align: right"]$757.85[/TD]
[/TR]
[TR]
[TD]HQ[/TD]
[TD]Warehouse Monitor[/TD]
[TD]Computer Equipment[/TD]
[TD="align: right"]$108.53[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

Your last part is incorrect. You should be summing column E, not column C. You cannot sum a Text column, which is why you are getting the #VALUE error.

 

[jrc999]

Well, now I feel really silly! My problem isn't forgetting my old skills, it's poor eyesight and lack of coffee this morning!

Thank you for your very kind assistance!!! And for the welcome!!

 

[Joe4]

You are welcome!

My problem isn't forgetting my old skills, it's poor eyesight and lack of coffee this morning!

No worries. Trust me, I know the feeling! Had to buy my first pair of reading glasses this year (and I used to have 20/10 vision!).

Glad to have you aboard!