Sumproduct Problem???

[Sean]

What is wrong with my formula?

=SUMPRODUCT((I13:I42<>{"OFF","VAC",""})*(D1342<>{"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]

What is wrong with my formula?

=SUMPRODUCT((I13:I42<>{"OFF","VAC",""})*(D1342<>{"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<>"")*(D1342<>"FILL")*(D1342<>"")*(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",""})*(D1342<>{"FILL",""})

hit escape

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

now hit escape
Select
(D1342<>{"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.