Forum Discussion

igrigorjevs's avatar
igrigorjevs
Copper Contributor
Sep 19, 2022
Solved

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's avatar
    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's avatar
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")))

or

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

Adjust the range as needed.

  • igrigorjevs's avatar
    igrigorjevs
    Copper Contributor
    Sep 19, 2022
    Thanks a lot
    =SUMPRODUCT(--(Sheet1!A2:A1000-DAY(Sheet1!A2:A1000)=C34-DAY(C34)))
    Working perfectly!

Resources