I am using the following formula:
=SUMPRODUCT(($A2:$A3702847300000)*($A2:$A3702<847 399999)*($G2:$G37020)*($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:$A37847300000))*( IF(ISNUMBER($A2:$A37),$A2:
$A37<847399999))*(IF(ISNUMBER($G2:$G37),$G2:$G370 ))*($D2:$D37))

entered with ctrl + shift & enter
--

Regards,

Peo Sjoblom



"Josh O." wrote in message
...
I am using the following formula:

=SUMPRODUCT(($A2:$A3702847300000)*($A2:$A3702<847 399999)*($G2:$G37020)*($G
2:$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?

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?

"Peo Sjoblom" wrote:

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:$A37847300000))*( IF(ISNUMBER($A2:$A37),$A2:
$A37<847399999))*(IF(ISNUMBER($G2:$G37),$G2:$G370 ))*($D2:$D37))

entered with ctrl + shift & enter
--

Regards,

Peo Sjoblom



"Josh O." wrote in message
...
I am using the following formula:

=SUMPRODUCT(($A2:$A3702847300000)*($A2:$A3702<847 399999)*($G2:$G37020)*($G
2:$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?

Assuming the values error come from the column D

=SUM((IF(ISNUMBER($A2:$A3702),$A2:$A3702847300000 ))*(IF(ISNUMBER($A2:$A3702
),$A2:$A37<847399999))*(IF(ISNUMBER($G2:$G3702),$G 2:$G37020))*(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



"Josh O." wrote in message
...
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?

"Peo Sjoblom" wrote:

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:$A37847300000))*( IF(ISNUMBER($A2:$A37),$A2:
$A37<847399999))*(IF(ISNUMBER($G2:$G37),$G2:$G370 ))*($D2:$D37))

entered with ctrl + shift & enter
--

Regards,

Peo Sjoblom



"Josh O." wrote in message
...
I am using the following formula:


=SUMPRODUCT(($A2:$A3702847300000)*($A2:$A3702<847 399999)*($G2:$G37020)*($G
2:$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?

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

"Peo Sjoblom" wrote:

Assuming the values error come from the column D

=SUM((IF(ISNUMBER($A2:$A3702),$A2:$A3702847300000 ))*(IF(ISNUMBER($A2:$A3702
),$A2:$A37<847399999))*(IF(ISNUMBER($G2:$G3702),$G 2:$G37020))*(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



"Josh O." wrote in message
...
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?

"Peo Sjoblom" wrote:

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:$A37847300000))*( IF(ISNUMBER($A2:$A37),$A2:
$A37<847399999))*(IF(ISNUMBER($G2:$G37),$G2:$G370 ))*($D2:$D37))

entered with ctrl + shift & enter
--

Regards,

Peo Sjoblom



"Josh O." wrote in message
...
I am using the following formula:


=SUMPRODUCT(($A2:$A3702847300000)*($A2:$A3702<847 399999)*($G2:$G37020)*($G
2:$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?

I 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." wrote in message
...
How do fix the value error? It comes from a vlookup formula for customers
that are not on a particular report.

"Peo Sjoblom" wrote:

Assuming the values error come from the column D


=SUM((IF(ISNUMBER($A2:$A3702),$A2:$A3702847300000 ))*(IF(ISNUMBER($A2:$A3702

),$A2:$A37<847399999))*(IF(ISNUMBER($G2:$G3702),$G 2:$G37020))*(IF(ISNUMBE
R(
$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



"Josh O." wrote in message
...
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?

"Peo Sjoblom" wrote:

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:$A37847300000))*( IF(ISNUMBER($A2:$A37),$A2:
$A37<847399999))*(IF(ISNUMBER($G2:$G37),$G2:$G370 ))*($D2:$D37))

entered with ctrl + shift & enter
--

Regards,

Peo Sjoblom



"Josh O." wrote in message
...
I am using the following formula:



=SUMPRODUCT(($A2:$A3702847300000)*($A2:$A3702<847 399999)*($G2:$G37020)*($G
2:$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?