G
Guest
I have a formula
=SUMPRODUCT((MONTH($A50)=MONTH(Data!$H$2:$H$15000))*(YEAR($A50)=YEAR(Data!$H$2:$H$15000)))
This works fine when there are dates in Col H of the worksheet "Data".
The data in worksheet "Data" is imported (via a macro) from a CSV file as a
web query and parsed using Data, Text to Columns.
Occasionally a user will have added a spurious comma in the original data
source which pushes all the remaining fields for that row to the right. When
this happens Col H on the worksheet "Data" can contain text instead of a date
and I get a #Value! error from my Sumproduct formula.
Is it possible to amend the formula slightly so if there is text in Col H it
is ignored?
The sheet with the formula has over 400 Sumproduct formulas based on lots of
other more complex conditions so I'm hoping there's an easy solution but I'm
afraid I can't see it.
Thanks a lot for your help
=SUMPRODUCT((MONTH($A50)=MONTH(Data!$H$2:$H$15000))*(YEAR($A50)=YEAR(Data!$H$2:$H$15000)))
This works fine when there are dates in Col H of the worksheet "Data".
The data in worksheet "Data" is imported (via a macro) from a CSV file as a
web query and parsed using Data, Text to Columns.
Occasionally a user will have added a spurious comma in the original data
source which pushes all the remaining fields for that row to the right. When
this happens Col H on the worksheet "Data" can contain text instead of a date
and I get a #Value! error from my Sumproduct formula.
Is it possible to amend the formula slightly so if there is text in Col H it
is ignored?
The sheet with the formula has over 400 Sumproduct formulas based on lots of
other more complex conditions so I'm hoping there's an easy solution but I'm
afraid I can't see it.
Thanks a lot for your help