Automatically moving data

  • Thread starter Thread starter multiplan
  • Start date Start date
M

multiplan

I got noting but zeros.. :confused:


0 =SUMPRODUCT((Sheet1!$A$1:$A$10=$A$1)*(Sheet1!$B$1:$B$10=B2)*(Sheet1!$C$1:$C$10="fries"),Sheet1!$D$1:$D$10)

0 =SUMPRODUCT((Sheet1!$A$1:$A$10=$A$1)*(Sheet1!$B$1:$B$10=B3)*(Sheet1!$C$1:$C$10="fries"),Sheet1!$D$1:$D$10)

0 =SUMPRODUCT((Sheet1!$A$1:$A$10=$A$1)*(Sheet1!$B$1:$B$10=B4)*(Sheet1!$C$1:$C$10="fries"),Sheet1!$D$1:$D$10)

0 =SUMPRODUCT((Sheet1!$A$1:$A$10=$A$1)*(Sheet1!$B$1:$B$10=B5)*(Sheet1!$C$1:$C$10="fries"),Sheet1!$D$1:$D$10)



0 =SUMPRODUCT((Sheet1!$A$1:$A$10=$A$7)*(Sheet1!$B$1:$B$10=B8)*(Sheet1!$C$1:$C$10="Fries"),Sheet1!$D$1:$D$10)

0 =SUMPRODUCT((Sheet1!$A$1:$A$10=$A$7)*(Sheet1!
$B$1:$B$10=B9)*(Sheet1!$C$1:$C$10="Fries"),Sheet1!$D$1:$D$10)

0 =SUMPRODUCT((Sheet1!$A$1:$A$10=$A$7)*(Sheet1!
$B$1:$B$10=B10)*(Sheet1!$C$1:$C$10="Fries"),Sheet1!$D$1:$D$10)

0 =SUMPRODUCT((Sheet1!$A$1:$A$10=$A$7)*(Sheet1!$B$1:$B$10=B11)*(Sheet1!$C$1:$C$10="Fries"),Sheet1!$D$1:$D$10)



0 =SUMPRODUCT((Sheet1!$A$1:$A$10=$A$13)*(Sheet1!$B$1:$B$10=B14)*(Sheet1!$C$1:$C$10="Fries"),Sheet1!$D$1:$D$10)

0 =SUMPRODUCT((Sheet1!$A$1:$A$10=$A$13)*(Sheet1!$B$1:$B$10=B15)*(Sheet1!$C$1:$C$10="Fries"),Sheet1!$D$1:$D$10)

0 =SUMPRODUCT((Sheet1!$A$1:$A$10=$A$13)*(Sheet1!$B$1:$B$10=B16)*(Sheet1!$C$1:$C$10="Fries"),Sheet1!$D$1:$D$10)

0 =SUMPRODUCT((Sheet1!$A$1:$A$10=$A$13)*(Sheet1!$B$1:$B$10=B17)*(Sheet1!$C$1:$C$10="Fries"),Sheet1!$D$1:$D$10
 
Welcome back !
Had almost expired monitoring of this thread <g>

Perhaps you could email over a small zipped? copy of your file
I'll take a look .. Easier that way ..

Just too many possibilities on what could have gone wrong,
although I thought your original post did describe the set-up
adequately, and the earlier response did address sufficiently

Do drop me a line in reply here (promptly, pl)
if you're sending the file over or otherwise

In the interim, you could also try something along the lines of
the revised formulas below:

With TRIM() added around all the ranges
(this'll help clear up any extraneous spaces and improve matching)
and
With the ranges amended to cover the
# of rows closer to your *actual* range
(just in case there's really no "fries" within the first 10 rows in col C)

In your sheet: Fries Sold
-----------------------
Put in B2:

=SUMPRODUCT((TRIM(Sheet1!$A$1:$A$2000)=$A$1)*(TRIM(Sheet1!$B$1:$B$2000)=A2)*
(TRIM(Sheet1!$C$1:$C$2000)="Fries"),Sheet1!$D$1:$D$2000)

Copy down to B5
 
On 3rd thought ..
In your sheet: Fries Sold
-----------------------
Put in B2:

=SUMPRODUCT((TRIM(Sheet1!$A$1:$A$2000)=$A$1)*(TRIM(Sheet1!$B$1:$B$2000)=A2)*
(TRIM(Sheet1!$C$1:$C$2000)="Fries"),Sheet1!$D$1:$D$2000)
Click to expand...

Might as well play really safe? and TRIM() the lookup values as well ..

So, put instead in B2:
=SUMPRODUCT((TRIM(Sheet1!$A$1:$A$2000)=TRIM($A$1))*(TRIM(Sheet1!$B$1:$B$2000
)=TRIM(A2))*(TRIM(Sheet1!$C$1:$C$2000)="Fries"),Sheet1!$D$1:$D$2000)
 
Perhaps you could email over a small zipped? copy of your file

Either to:

xdemechanik <at>yahoo<dot>com
or
demechanik <at>yahoo<dot>com

(both valid)
 
Back
Top