Sumproduct Problem???
 

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,

Sumproduct Problem???
 

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

Sumproduct Problem???
 

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,