Help with SUMIF using criteria in two columns - SUMPRODUCT?

jrc999

New Member
Joined
Jan 5, 2018
Messages
7
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!!


Col BCol CCol DCol E
LocationItemCategoryCost
CA48 Port SwitchNetwork Equipment$1,479.00
CA24 Port SwitchNetwork Equipment$925.00
CACable MaterialsNetwork Infrastructure$4,250.00
CACable LaborNetwork Infrastructure$18,100.00
CAUniFi access pointsNetwork Equipment$130.00
CAWarehouse ComputersComputer Equipment$757.85
HQWarehouse MonitorComputer Equipment$108.53

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

jrc999

New Member
Joined
Jan 5, 2018
Messages
7
Well, now I feel really silly! My problem isn't forgetting my old skills, it's poor eyesight and lack of coffee this morning! :LOL:

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,492
Messages
5,601,994
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top