Sumproduct Problem???

[Sean]

What is wrong with my formula?

=SUMPRODUCT((I13:I42<>{"OFF","VAC",""})*(D13:D42<>{"FILL",""})*C13:C42)

In Cells C13:42 I have 1's where I want them, yet this keeps coming back
with #N/A, if I remove the last "" after "FILL" it comes up with a total of
46 when it should only come up with 12!!!

Any help would be great...

Thanks,

[Franz Verga]

Sean wrote:
> What is wrong with my formula?
>
> =SUMPRODUCT((I13:I42<>{"OFF","VAC",""})*(D13:D42<>{"FILL",""})*C13:C42)
>
> In Cells C13:42 I have 1's where I want them, yet this keeps coming
> back with #N/A, if I remove the last "" after "FILL" it comes up with
> a total of 46 when it should only come up with 12!!!
>
> Any help would be great...
>
> Thanks,

Hi Sean,

try this way:

=SUMPRODUCT((I13:I42<>"OFF")*(I13:I42<>"VAC")*(I13:I42<>"")*(D13:D42<>"FILL")*(D13:D42<>"")*(C13:C42))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[Tom Ogilvy]

in the formula bar, select this much of your equation and hit F9

(I13:I42<>{"OFF","VAC",""})*(D13:D42<>{"FILL",""})

hit escape

Now select each individual
(I13:I42<>{"OFF","VAC",""})
hit F9

now hit escape
Select
(D13:D42<>{"FILL",""})
Hit F9

now hit escape.

you are multiplying and (n x 3) array against an (n x 2) array. since it
multiplies element by element, there is a mismatch and you get a column of
N/A.

If you take out the "", then you multiple each column of the (n x 3) array
against the single column of the adjusted section.

now with this single condition for the second part, you check each cell in
column I for 3 conditions. So each row will produce at minimum 2 Trues and
the rows you really want to count will produce 3 trues. So your formula is
not logically correct.

--
Regards,
Tom Ogilvy


"Sean" wrote:

> What is wrong with my formula?
>
> =SUMPRODUCT((I13:I42<>{"OFF","VAC",""})*(D13:D42<>{"FILL",""})*C13:C42)
>
> In Cells C13:42 I have 1's where I want them, yet this keeps coming back
> with #N/A, if I remove the last "" after "FILL" it comes up with a total of
> 46 when it should only come up with 12!!!
>
> Any help would be great...
>
> Thanks,
>
>