Sumproduct not working

[blossomthe2nd]

Hi guys

I am trying to do a sumproduct array formula and I keep getting an error, #Value! , the following is the part of the arrya that seems to be causing it , can anyone see an issue with it please ?

Thanks

=SUMPRODUCT(--(MONTH('[Logs Master.xls]Customer Service'!$B$4:$B$10003)=7),--('[Logs Master.xls]Customer Service'!$H$4:$H$10003="Static query"),--('[Logs Master.xls]Customer Service'!$B$4:$B$10003="Yes"))

 

[blossomthe2nd]

Saw my issue, last part of array should have been J 4 j 1004 not bs, and there was invalid data within this array anyway !!

Thanks

 

[Andrew Poulsom]

Are you sure that there are no text entries in column B?

 

[T. Valko]

Hi guys

I am trying to do a sumproduct array formula and I keep getting an error, #Value! , the following is the part of the arrya that seems to be causing it , can anyone see an issue with it please ?

Thanks

=SUMPRODUCT(--(MONTH('[Logs Master.xls]Customer Service'!$B$4:$B$10003)=7),--('[Logs Master.xls]Customer Service'!$H$4:$H$10003="Static query"),--('[Logs Master.xls]Customer Service'!$B$4:$B$10003="Yes"))

You're testing the same range for 2 different conditions:

(MONTH('[Logs Master.xls]Customer Service'!$B$4:$B$10003)=7)

('[Logs Master.xls]Customer Service'!$B$4:$B$10003="Yes")

If the range contains non-date entries then the test for the MONTH will generate #VALUE! errors.

However, from a logical standpoint, that formula will never work because that range can't meet both conditions at the same time!