SumProduct Formula Error

SumProduct Formula Error

Post by VG9kZ » Fri, 24 Sep 2004 03:31:04


Hi. I am trying to use the sumproduct formula to match names from one
worksheet to another and then copy data if there is a match. My test formula
works but when I put the sheet names in I get VALUE? I know the cells have
matches (at least C5 does). Can someone look and see where I went wrong?


Thanks

Todd

Here is my test formula (which works)
=SUMPRODUCT(--(($G$8:$G$20)=(AT16)),$J$8:$J$20)

And here is the formula with the worksheet names included.
=SUMPRODUCT(--($C$2:$C$500)='[Phoenix-Tucson - CO Sept 2004
VoluntaryProducts.tl.xls]Sept2004'!$C5,'[Phoenix-Tucson - CO Sept 2004
VoluntaryProducts.tl.xls]Sept2004'!$Z$2:$Z$500)
 
 
 

SumProduct Formula Error

Post by d2VlbGl0dG » Fri, 24 Sep 2004 05:13:07

try sumif and see what happens. I think sum product will only work with
numbers.

 
 
 

SumProduct Formula Error

Post by Frank Kabe » Fri, 24 Sep 2004 05:20:33

Hi
first try correcting the brackets:
=SUMPRODUCT(--($C$2:$C$500='[Phoenix-Tucson - CO Sept 2004
VoluntaryProducts.tl.xls]Sept2004'!$C5),'[Phoenix-Tucson - CO Sept 2004
VoluntaryProducts.tl.xls]Sept2004'!$Z$2:$Z$500)

also check if the formula works if the other file is open

"Todd" < XXXX@XXXXX.COM > schrieb im Newsbeitrag

one
formula
cells have
wrong?
2004
 
 
 

SumProduct Formula Error

Post by Frank Kabe » Fri, 24 Sep 2004 05:21:01

Hi
SUMIF won't work as the other file is probably closed and SUMIF won't
work on closed files

"weelittlekim" < XXXX@XXXXX.COM > schrieb im

with

one
test formula
cells have
wrong?
2004
 
 
 

SumProduct Formula Error

Post by VG9kZ » Fri, 24 Sep 2004 06:03:04

Thanks Frank. Sumproduct has become my fav formula (thanks to all your help
in the past.) I had the formula columns backwards!

Todd L.
 
 
 

SumProduct Formula Error

Post by Sm9zaCBPL » Sun, 13 Feb 2005 05:35:14

I am using the following formula:
=SUMPRODUCT(($A2:$A3702>847300000)*($A2:$A3702<847399999)*($G2:$G3702>0)*($G2:$G3702<31),$D2:$D3702)

Because the cells in the columns use a formula that sometimes returns a #N/A
result, the result of the above formula is always #N/A. Is there a way to
alter the formula so that is only adds the numbers and returns the sum of the
D column for all the cells that meet the enclosed criteria?
 
 
 

SumProduct Formula Error

Post by Peo Sjoblo » Sun, 13 Feb 2005 05:59:22

You would be better off if you fixed the #N/A like

=IF(ISNA(formula),"",formula)


regardless in what column do they (N/A) appear? If they are in one
particular column you could just use
the IF for that particular column, otherwise you could use something like

=SUM((IF(ISNUMBER($A2:$A37),$A2:$A37>847300000))*(IF(ISNUMBER($A2:$A37),$A2:
$A37<847399999))*(IF(ISNUMBER($G2:$G37),$G2:$G37>0))*($D2:$D37))

entered with ctrl + shift & enter
--

Regards,

Peo Sjoblom





=SUMPRODUCT(($A2:$A3702>847300000)*($A2:$A3702<847399999)*($G2:$G3702>0)*($G
2:$G3702<31),$D2:$D3702)
#N/A
the
 
 
 

SumProduct Formula Error

Post by Sm9zaCBPL » Sun, 13 Feb 2005 06:25:03

Okay, is there a way to fix a #VALUE!? In the same cell? The formulas in
the cell range can return "#N/A" if a customer number is found but not an
invoice number, and "#VALUE!" if the customer number is not found.

I tried the formula you have below (with the correct cell ranges), but I am
getting a #VALUE! error. Is that because there are #VALUE! errors in the
cell ranges?
 
 
 

SumProduct Formula Error

Post by Peo Sjoblo » Sun, 13 Feb 2005 07:03:59

Assuming the values error come from the column D

=SUM((IF(ISNUMBER($A2:$A3702),$A2:$A3702>847300000))*(IF(ISNUMBER($A2:$A3702
),$A2:$A37<847399999))*(IF(ISNUMBER($G2:$G3702),$G2:$G3702>0))*(IF(ISNUMBER(
$D2:$D3702),$D2:$D3702)))

also array entered

value errors come from calculated text, (for exam;e =1+"a") you should be
able to fix that as well, that way you could use
your original formula


--

Regards,

Peo Sjoblom





am

like
=SUM((IF(ISNUMBER($A2:$A37),$A2:$A37>847300000))*(IF(ISNUMBER($A2:$A37),$A2:


=SUMPRODUCT(($A2:$A3702>847300000)*($A2:$A3702<847399999)*($G2:$G3702>0)*($G
a
way to
of
 
 
 

SumProduct Formula Error

Post by Sm9zaCBPL » Wed, 16 Feb 2005 06:13:04

How do fix the value error? It comes from a vlookup formula for customers
that are not on a particular report.
 
 
 

SumProduct Formula Error

Post by Peo Sjoblo » Wed, 16 Feb 2005 06:23:17

don't think vlookup can retrun a value erro unless you make a calculation
with the result or if
there are value errors in the lookup table itself. If you are making a
calculation like n*vlookup
and vlookup sometimes returns a "" or a text?

=IF(ISTEXT(vlookup),0,vlookup)

or if the vlookup gives the error

=IF(ISERROR(vlookup),0,vlookup)

something like that

--

Regards,

Peo Sjoblom


"Josh O." < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
=SUM((IF(ISNUMBER($A2:$A3702),$A2:$A3702>847300000))*(IF(ISNUMBER($A2:$A3702
R(
be
formulas in
an
I
the
=SUM((IF(ISNUMBER($A2:$A37),$A2:$A37>847300000))*(IF(ISNUMBER($A2:$A37),$A2:
=SUMPRODUCT(($A2:$A3702>847300000)*($A2:$A3702<847399999)*($G2:$G3702>0)*($G
returns
a
sum