SOLVED

New Contributor

# Sumproduct with dates issue

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")))

3 Replies

# Re: Sumproduct with dates issue

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 (New Contributor)
Solution

# Re: Sumproduct with dates issue

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.

# Re: Sumproduct with dates issue

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