If you’re using a date column as the criteria column in Google Sheets, you may encounter incorrect results in formulas like Conditional Count, Sum, Product, etc., particularly when it comes to the month of December. In this article, we will explore how to solve this common issue and ensure accurate calculations in your Google Sheets formulas.
Understanding the Root Cause
Before we dive into the solutions, let’s understand why this issue occurs in the first place. When working with date functions in Google Sheets, a blank cell is interpreted as 30/12/1899. This means that formulas using a blank cell as the end date may produce unexpected results.
To illustrate this, let’s consider the DATEDIF function. The syntax of this function is as follows:
DATEDIF(start_date, end_date, unit)When you use the DATEDIF function with today() as the start date and a blank cell as the end date, the formula will return #NUM. This is because the end date should be greater than or equal to the start date.
Solving the 12th-Month Issue
Now that we understand the root cause, let’s explore how to solve the 12th-month issue in formulas in Google Sheets. We’ll go through some basic examples using popular functions like COUNTIF, SUMIF, QUERY, and SUMPRODUCT.
Countif – Wrong Count in 12th-Month
Let’s start with the COUNTIF formula. Suppose we have a range B2:B15 containing date entries, and we want to count the number of entries that fall in the month of December. The formula would be:
=ArrayFormula(COUNTIF(MONTH(B2:B15),12))However, this formula may produce incorrect results if there are blank cells within the range. To solve this, we can use the FILTER function to apply the formula only to non-blank cells:
=ArrayFormula(COUNTIF(MONTH(FILTER(B2:B15,B2:B15>0)),12))Sumif – December Month Issue in Sumif
Next, let’s consider the SUMIF formula. Suppose we want to sum the values in column C if the corresponding date in column B falls in the month of December. The formula would be:
=ArrayFormula(SUMIF(MONTH(B2:B15),12,C2:C15))Similar to the COUNTIF formula, this formula may include values from blank cells and produce incorrect results. To solve this, we can use the SUMIFS function along with the FILTER function:
=ArrayFormula(SUMIFS(C2:C15,MONTH(FILTER(B2:B15,B2:B15>0)),12,B2:B15,">0"))Query – How to Solve 12th-Month Issue in Query
The QUERY function in Google Sheets operates differently from other functions as it runs the Google Visualization API Query Language Query. In the QUERY function, the month number ranges from 0 to 11, with 11 representing December.
To demonstrate this, let’s use the same dataset from the previous examples. Suppose we want to sum column C if column B‘s month is December. The formula would be:
=QUERY({B1:C15},"SELECT SUM(Col2) WHERE MONTH(Col1)=11",1)However, this formula may return incorrect results due to the way blank cells are interpreted. To correct it, we can use the FILTER function to exclude blank cells:
=QUERY({FILTER(B2:C15,B2:B15>0)},"SELECT SUM(Col2) WHERE MONTH(Col1)=11",1)Sumproduct – Wrong Product in 12th Month
Lastly, let’s consider the SUMPRODUCT formula. Suppose we want to conditionally sum the values in column C based on the month of the corresponding date in column B. The formula would be:
=SUMPRODUCT(MONTH(B2:B15)=12,C2:C15)Similar to the previous formulas, this formula may include values from blank cells and produce incorrect results. To solve this, we can use the FILTER function:
=SUMPRODUCT(MONTH(FILTER(B2:B15,B2:B15>0))=12,FILTER(C2:C15,B2:B15>0))By utilizing the FILTER function, we can ensure accurate calculations even when working with the 12th month in Google Sheets formulas.
That’s all for today! If you encounter any issues with other functions related to the 12th month, feel free to ask for assistance in the comments. Keep on learning and enjoy your Google Sheets journey!
