Forum Discussion
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")))
=SUMPRODUCT((LEFT(Sheet1!A:A,7)=TEXT(C34,"yyyy-mm")))
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.
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!
- 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?
