Forum Discussion
SUMIFS Question - All But One Criteria Work
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.
- 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]
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.
- The latter worked without the "="& which is perfect I didn't know the [@Customer] had to be outside the quotations, that explains a lot.
- 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]- 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!
