SOLVED

SUMIFS Question - All But One Criteria Work

New 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:

=IF([@Customer]="","",COUNTIFS(Orders[Customer],[@Customer],Orders[Shipped],">=4/1/2022",Orders[Shipped],"<5/1/2022"))

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:

=IF([@Customer]="","",SUMIFS(Orders[Pcs],Orders[Shipped],">=4/1/2022",Orders[Shipped],"<5/1/2022",Orders[Customer],"=[@Customer]"))

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

@and_rogynous 

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

 

=IF([@Customer]="","",SUMIFS(Orders[Pcs],Orders[Shipped],">=4/1/2022",Orders[Shipped],"<5/1/2022",Orders[Customer],[@Customer]))

 

Or else take [@Customer] outside the quotes:

 

=IF([@Customer]="","",SUMIFS(Orders[Pcs],Orders[Shipped],">=4/1/2022",Orders[Shipped],"<5/1/2022",Orders[Customer],"="&[@Customer]))

 

but that seems unnecessary.

best response confirmed by and_rogynous (New Contributor)
Solution
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!