How to use current year in SUMIF statements?

Support
1

I’d like to use SUMIF on a column of values in a subtable, to get a sum for “this year.”

I saw the note saying I can’t use TODAY() or NOW() directly in a SUMIF statement, so I set up a separate TODAY() statement in a separate field. I’ve tried SUMIF([year field reference in subtable], year([field reference with TODAY()], [field reference in subtable for values to be added]) but that doesn’t work.

How can I use SUMIF to add values based on the year corresponding to today’s date?

2

Hi,

You may consider following the step-by-step instructions below.

Let’s say the subtable field with the TODAY() formula applied is in cell A10, and the subtable field to be added up using the SUMIF() formula is in cell B10.

  1. Create a new numeric field in the subtable (Let’s say this subtable field is on C10) and set a YEAR() formula to reference A10. This formula will get the year value of a date field. For this example, the formula should be YEAR(A10)
  2. Create a new independent field (Let’s say this field is on A15) and set the formula TODAY() or TODAYTZ() to obtain the current date.
  3. Create another independent field (A16) and apply the YEAR() formula referencing field A15 to obtain the year value. For this example, the formula should be YEAR(A15)
  4. Create the final independent field for applying the SUMIF() formula. The formula should be SUMIF(C10,A16,B10)