SUMPRODUCT formula error

[theticklives]

hi
this is my first post, after having read many threads and searched for answers successfully.

My .xls is a 2003 version and is summing injury data. I am trying to look up info on another sheet meeting criteria. i have been successful with the formula with 2 arrays, but adding the third array seems to cause error.

I have a sumproduct formula that returns an #value error (data is of the wrong type):
=SUMPRODUCT(--('Incident log'!A4:A11="Jan"),--('Incident log'!F1:F11="resins"),--('Incident log'!G1:G11="UNSAFE CONDITION"))

I am searching a spreadsheet of text and converting to true false with the --. This works well for the 1st two arrays, but it seems like the last array does not get converted to the 1 or 0, resulting in the formula error. i got this far with some help here at work, but we cant figure this out.


ANY help would be great
thx in advance!
theticklives

 

[Jonmo1]

Welcome to the board...

Best I can see is you get the error because not all ranges are the same size...

=SUMPRODUCT(--('Incident log'!A4:A11="Jan"),--('Incident log'!F1:F11="resins"),--('Incident log'!G1:G11="UNSAFE CONDITION"))

They should all be either rows 1-11 or 4-11, not a mixture of the two..

Hope this helps..

 

[Mike Blackman]

Hi,

Your three ranges all need to equal the same range;

Your first Array is A4:A11,
Second is F1:F11
Third is G1:G11

The first should read A1:A11 or the others F4:G11 for this to work.