SOLVED

Sumproduct with dates issue

Copper Contributor
Require to check dates and get number of requests by month but result is 0
=SUMPRODUCT((LEFT(Sheet1!A:A,7)=TEXT(C34,"yyyy-mm")))

Screenshot from 2022-09-19 16-50-52.pngScreenshot from 2022-09-19 16-50-10.png
3 Replies
If column A is an actual date then you need to use that same TEXT(A7,"YYYY-MM") function to convert it to text before (actually instead of) taking LEFT(xxx, 7).
Also are you aware you are doing a COUNTIF()? basically you are summing up the number of cases that the left and right are equal?
best response confirmed by igrigorjevs (Copper Contributor)
Solution

@igrigorjevs 

The problem is that Excel stores dates as numbers. The LEFT function looks at these numbers, not at the formatted dates. Moreover, the result of the comparison is TRUE or FALSE, and SUMPRODUCT ignores such values.

Use

=SUMPRODUCT(--(TEXT(Sheet1!A2:A1000,"yyyy-mm")=TEXT(C34,"yyyy-mm")))

or

=SUMPRODUCT(--(Sheet1!A2:A1000-DAY(Sheet1!A2:A1000)=C34-DAY(C34)))

Adjust the range as needed.

Thanks a lot
=SUMPRODUCT(--(Sheet1!A2:A1000-DAY(Sheet1!A2:A1000)=C34-DAY(C34)))
Working perfectly!
1 best response

Accepted Solutions
best response confirmed by igrigorjevs (Copper Contributor)
Solution

@igrigorjevs 

The problem is that Excel stores dates as numbers. The LEFT function looks at these numbers, not at the formatted dates. Moreover, the result of the comparison is TRUE or FALSE, and SUMPRODUCT ignores such values.

Use

=SUMPRODUCT(--(TEXT(Sheet1!A2:A1000,"yyyy-mm")=TEXT(C34,"yyyy-mm")))

or

=SUMPRODUCT(--(Sheet1!A2:A1000-DAY(Sheet1!A2:A1000)=C34-DAY(C34)))

Adjust the range as needed.

View solution in original post