SUMIFS formula help

Support
1

I am trying to create a formula to sum the total of transactions between two dates and I can’t get it to work. The data needs to come from my referenced subtable. So basically the formula should be like SUMIFS(D21,A21,">=2019/01/01",A21,"<=2019/01/31"). What am I doing wrong? I have tried multiple ways by changing the date format, adding the & symbol, adding seperate cells for the start and end date (so it references A4 and A5 instead of the actual date) Please Help!

2

Hi there,
Beside creating two reference date fields (e. g. “Start” field to enter the value 2019/01/01, and “End” field to enter the value 2019/01/31. Say the coordinate of “Start” is A4 and “End” is A5, the formula should then be written as SUMIFS(D21,A21,">=A4",A21,"<=A5"), make sure that the field where you apply the formula is a numeric field.

Cheers,
Aletha

3

Hi aletha!

I am having similar issues with this, I have done as you requested but when asking the AI tool for help it gives me an error: “I noticed you’re comparing a Non-Date Field with a Date Field. Please create a new subtable field first and apply the DATEVALUE() formula to generate the date value for the Non-Date Field.”

My example is running multiple criteria based on status and whether or not the date of the sales order is within the current month (see below for the example). Why is giving me this error if everywhere it is looking for a date for the range is actually a date and the only thing that is not a date is the actual summed range??

SUMIFS(E14,A14,“>=H5”,A14,“<=H6”,F14,“Submitted”,F14,“Approval Needed”,F14,“Approved”,F14,“Order Confirmed”,F14,“Packaged/Loaded”)

4

Hi there!

This is Kate from Ragic Support. The error message from the AI tool may not always point to the exact root cause, but based on your formula, I suspect the issue might be related to how the criteria for field F14 is structured.

In your current formula:

SUMIFS(E14, A14, “>=H5”, A14, “<=H6”, F14, “Submitted”, F14, “Approval Needed”, F14, “Approved”, F14, “Order Confirmed”, F14, “Packaged/Loaded”)

You’re applying multiple criteria to the same field (F14). This means the formula is attempting to filter for rows where F14 equals all of those values at the same time, which isn’t possible.

From your description, it sounds like you’re trying to sum values in E14 when:

  1. The date in A14 falls between the values in H5 and H6, and
  2. The status in F14 is any one of the following: “Submitted”, “Approval Needed”, “Approved”, “Order Confirmed”, or “Packaged/Loaded”.

If that’s the case, I recommend rewriting the formula like this:

SUMIFS(E14, A14, ">=H5", A14, "<=H6", F14, "Submitted") +
SUMIFS(E14, A14, ">=H5", A14, "<=H6", F14, "Approval Needed") +
SUMIFS(E14, A14, ">=H5", A14, "<=H6", F14, "Approved") +
SUMIFS(E14, A14, ">=H5", A14, "<=H6", F14, "Order Confirmed") +
SUMIFS(E14, A14, ">=H5", A14, "<=H6", F14, "Packaged/Loaded")

This breaks the logic into multiple parts, summing the values that match each status individually and then adding them together. When entering the formula into the system, please make sure to remove any extra spaces or line breaks; they’ve been added here solely for readability.

Please give this a try! If your use case is a bit different from what I’ve described, feel free to share more details and I’d be happy to take another look.