SUMIFS Question - All But One Criteria Work

Occasional Contributor

I have a spreadsheet to keep track of shipments I sent out at my job.

The first table is called Orders and has the relevant columns Customer (name), Shipped (MM/DD/YYYY), Carrier (name), and Pcs (number of skids in the shipment). The second table is called CustomerShipCount and has the relevant columns Customer (name), [MM/YY] O (order count for the month for each customer), and [MM/YY] S (skid count for the month for each customer).

I'm able to get the order count done just fine; since each order gets its own row in the Orders table, the formula is:


However, the skid count is more difficult. I was hoping to use a SUMIFS formula to use all the necessary criteria to add the Pcs column together, like this:


Instead, it always shows 0. So, I started troubleshooting by testing each set of criteria on its own to see what the issue was. Everything works fine except for the final set, Orders[Customer],"=[@Customer]".

Why is this not working?

I'm using Office 365 on Windows 10 Pro.

4 Replies


Why not use the same criteria as in your order count formula?




Or else take [@Customer] outside the quotes:




but that seems unnecessary.

best response confirmed by and_rogynous (Occasional Contributor)
Everything inside of quotation marks is interpreted literally (so sumif would literally look for "[@Customer]" in the criteria range. Creating a criteria that includes a cell reference would have to be concatenated like:
Orders[Customer], "="&[@Customer]

When omitted, the "=" should be implied:
Orders[Customer], [@Customer]

The latter worked without the "="& which is perfect I didn't know the [@Customer] had to be outside the quotations, that explains a lot.
This worked perfectly! I didn't realize it had to be outside the quotations, and I didn't realize I could omit the "="& all together; thank you so much!