Forum Discussion
Sumproduct with dates issue
=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.
3 Replies
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?
