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

excel

HansVogelaar
Sep 19, 2022
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.

HansVogelaar

MVP

Sep 19, 2022

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

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

Adjust the range as needed.