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)

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

numbers.

numbers.

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

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

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

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

Thanks Frank. Sumproduct has become my fav formula (thanks to all your help

in the past.) I had the formula columns backwards!

Todd L.

in the past.) I had the formula columns backwards!

Todd L.

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(($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?

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

=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

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?

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?

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

=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

How do fix the value error? It comes from a vlookup formula for customers

that are not on a particular report.

that are not on a particular report.

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

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

1. Min Sumproduct formula returns a zero.

2. excel formula sumproduct and age ranges

3. COUNTIF or SUMPRODUCT formula

4. SUMPRODUCT formula to differentiate between Negative and Posit

5. Need to bring back Average using either Sumproduct or CSE formula

6. Sumif, SumProduct, CountIf Formula Help

7. Sumif, SumProduct, CountIf Formula Help

9. Need help with SUMPRODUCT Formula

10. Sumproduct formula not working with ranges

11. Hellp --- COUNTIF/SUMPRODUCT?? Large DataRange - Formula burps...

12. Help SUMPRODUCT Formula... must be missing something... ;o(

13. help in formula countif OR sumproduct

14. SUMPRODUCT formula using hours:minutes

15. sumproduct formula (multiple criteria)

11 post • Page:**1** of **1**