If you use a date column as the criteria column, sometimes you will get wrong results in Conditional Count, Sum, Product, etc. that when using the month December. This post explains how to solve this 12th-month issue in formulas in Google Sheets.
This post is a continuation of one of my earlier post titled How to Return Blank Instead of 30-12-1899 in Google Sheets Formulas.
Please read that first, if you haven’t already. Because in that post, I have explained the root cause of the 12th-month issue in formulas in Google Sheets.
Here is the reason in a nutshell with an example formula.
I hope you are all well familiar with the date functions in Google Sheets. DATEDIF is one of the useful date functions. The syntax of this function is as below.
DATEDIF(start_date, end_date, unit)Here I am going to write a formula based on this function. For that, first, make cell C3 blank, and in cell D4, insert the below formula.
=datedif(today(),C3)The formula would return #NUM because the start_date is today’s date and the end_date is blank. In Google Spreadsheets, blank represents 30/12/1899.
=datedif(today(),C3,"D")The reason for the error is the end_date. It must be greater than or equal to the start_date.
So the value of a blank cell (or 0 in a cell) is treated as 30/12/1899 by date functions.
Now let’s go to the root cause of the 12th-month issue in formulas in Google Sheets.
Try the below formula in any blank cell other than C3.
=month(C3)Even if C3 is blank, the formula will return 12, which is the month number of the value (blank) in the cell C3. Type 0 in C3. The result would again be 12.
It may cause issues in most of the formulas. Here I am going to help you solve the December month issue in some of the formulas in Google Sheets.
12th-Month Issue in Formulas in Google Sheets
I am giving you some basic examples using Countif, Sumif, Query, and Sumproduct. The same applies to Countifs, Sumifs, and some other functions.
You May Like: Google Sheets Function Guide [Quickly Learn All Popular Functions].
Countif – Wrong Count in 12th-Month
Let’s take a look at the below COUNTIF formula for the range of B2:B5.
=ArrayFormula(countif(month(B2:B15),12))
The formula counts the number of date entries in B2:B15 that falls in December month.
The actual entry is 6 (you can count manually to verify), but the formula wrongly returns 8 because of the two cells B11 and B12. To solve this, we can use the below method.
Apply the formula in a filtered range. That means replace B2:B15 reference in the formula with filter(B2:B15,B2:B15>0) as below.
=ArrayFormula(countif(month(filter(B2:B15,B2:B15>0)),12))Must Read: How to Use the Filter Function in Google Sheets [Basic and Advanced Use]
Sumif – December Month Issue in Sumif
Let’s conditionally sum the values in C2:C15 in the above same dataset. Here the condition is the month of December.
I mean sum C2:C15 if the month of the dates in B2:B15 is 12.
Here is the SUMIF formula that returns a wrong sum for the December month.
=ArrayFormula(sumif(month(B2:B15),12,C2:C15))Note:- I have modified the dataset slightly. It’s not the same that we have used in COUNTIF.
The formula would return 1525 as the conditional sum and which is wrong because the SUM includes the values in C11 and C12.
Here we can use the SUMIFS as below.
=ArrayFormula(sumifs(C2:C15,month(B2:B15),12,B2:B15,">0"))That means, to solve the 12th-Month issue in the SUMIF formula in Google Sheets, we can use the SUMIFS function. Also, please note that we can use some other formulas too.
I am not going to that to avoid any further confusion.
QUERY – How to Solve 12th-Month Issue in Query
The QUERY function is different from other functions in Google Sheets as it runs Google Visualisation API Query Language Query.
In Query, the month number is from 0 to 11. So the 11th month represents December, not the 12th.
So the same would reflect in the formulas too.
Let’s use the same above SUMIF/SUMIFS data set in the QUERY also. Here I am going to use the SUM aggregation function in Query.
You May Like: How to Sum, Avg, Count, Max, and Min in Google Sheets Query.
The below formula wrongly sums column C if column B’s month is December.
=query({B1:C15},"Select Sum(Col2) where month(Col1)=11",1)Unlike the wrong December month SUMIF formula, this formula would return 1325. Again it’s wrong. But in SUMIF, the sum was 1525.
Why is the difference in the output?
The QUERY doesn’t consider the month of a blank cell as December. But it does consider December if the cell contains 0.
To correct the QUERY, use FILTER to replace the range as below.
=query({filter(B2:C15,B2:B15>0)},"Select Sum(Col2) where month(Col1)=11",1)Let’s consider one more function, and that is SUMPRODUCT.
SUMPRODUCT – Wrong Product in 12th Month
Here is a conditional SUMPRODUCT formula.
=SUMPRODUCT(MONTH(B2:B15)=12,C2:C15)This SUMPRODUCT is wrong.
To solve the 12th-month issue in SUMPRODUCT in Google Sheets, we can use the FILTER.
=SUMPRODUCT(month(FILTER(B2:B15,B2:B15>0))=12,FILTER(C2:C15,B2:B15>0))That means FILTER plays a vital role in most of the functions to solve the December month issue in formulas in Google Sheets.
So if you face issues in any other functions related to the 12th month, use the FILTER. If that doesn’t solve it, I’ll try to solve your problem. Please share it with me in the comments.
That’s all. Thanks for the stay. Enjoy!
Resources:
