Calculate only date range.

Assuming you have not modified the Month and Year table, it has the first day of the month in cell A4 and the last day in A6. I believe "B2" will always have a date in it (which is what is causing the problem to begin with) so the condition for your IF statement does not need to check for B2<>"", instead it needs to check the dates. The condition for your IF function will be

AND(B2≥Month and Year::$A$4,B2≤Month and Year::$A$6)

If you actually do need to check for B2<>"", you can use IFS

IFS(B2="","", AND(B2≥Month and Year::$A$4,B2≤Month and Year::$A$6), your_SUMIF_formula, TRUE,"")

In the formula you posted, you used D2 twice and B2 twice. If the formula is for column D, all should be D2. It also seems to me that the rows that are showing the date (row 2, row 5, etc) are always populated with a date so there is no reason to check if D2<>"", it will always be true. Or did you modify something where it might not fill in the date?

Hi Pique5,

I am having trouble understanding how you modified the Calendar.

Did you Merge some cells?

What is the purpose of rows 3, 5 etc.?

I am also having trouble understanding your overall aim:

However, I want it to only calculate/show the days for that month (blue numbers) not the previous for next month (grey numbers).

I tested your formula in a simpler document (no Calendar).

I turned off Menu > Numbers > General > Cell References: Use header names as labels. That makes it easier to construct and debug formulas.

The formula works.

Notes:

• I formatted Date (Column B) in Table 1 as DD MMM YYYY to become more "international" for me and others who read this.
• Day Only (Column A) in Table 2 is a Custom Format borrowed from the Calendar Template. It displays only the Day part of a Date & Time format, while storing the complete Date & Time.

Perhaps a look "behind the "curtain" in the Calendar Template will put us all on the same wavelength.

The Calendar Template is very cleverly designed, but parts are hidden.

Click on each table and Format > Table > Table Options > Tick Title to see the table name.

Click on the MONTH AND YEAR table and Menu > Table > Un-hide All Rows.

The dates in Calendar are Custom Format to display only the day. Edit Custom Format to see more "behind the curtain".

Regards,

Ian.

You could have written your IF formula that same way and swapped the if_true and if_false parameters. Often with IF, the thing you want it to do (the SUMIF) is the if_true parameter and if_false is a secondary thing so the condition is written with that in mind (if B2<>"" then...). I guess it is human nature to write it that way.

With IFS, the first test had to be for B2="" because the next condition (the AND with the date comparisons) will fail with an error if B2 is "" rather than a date.