Why would the result for TotalR = 0 (when it should return 226,550.00)
whereas TotalO returns the correct amount.

TotalR = Sheets("Imported Requisition Data").Evaluate("SUMPRODUCT(--(L9:L" &
myRowsR + 8 & "=" & OrderR & "),(G9:G" & myRowsR + 8 & "))")

TotalO = Sheets("JDE Data").Evaluate("SUMPRODUCT(--(B6:B" & myRowsO + 10

I've also noticed that results for other variables related to the ("Imported
Requisition Data") sheet appear as "725", whereas the results from ("JDE
Data") sheet comes through as 725.

I think this is part of the problem, but I've checked that the data in both
sheets is formatted the same way, and now I don't know what else to try.

First thing I see in the first formula is that you're not coercing the second
half of the formula, try replacing
),(G9:G"...
with
),--(G9:G"...
although that really shouldn't affect the results the way you're using this.

As you noted, the format of the imported data appears to be a factor also -
the fact that it's showing up as "725" instead of just 725 indicates it's
stored as text instead of as a numeric value.

Try selecting all of the cells in that have the values shown with " "
surrounding them, and use Edit | Replace and select " as the character to
find and leave the replace with portion empty, click [Replace All]. I think
this is the one that's going to fix the problem.

"Helen" wrote:

Why would the result for TotalR = 0 (when it should return 226,550.00)
whereas TotalO returns the correct amount.

TotalR = Sheets("Imported Requisition Data").Evaluate("SUMPRODUCT(--(L9:L" &
myRowsR + 8 & "=" & OrderR & "),(G9:G" & myRowsR + 8 & "))")

TotalO = Sheets("JDE Data").Evaluate("SUMPRODUCT(--(B6:B" & myRowsO + 10

I've also noticed that results for other variables related to the ("Imported
Requisition Data") sheet appear as "725", whereas the results from ("JDE
Data") sheet comes through as 725.

I think this is part of the problem, but I've checked that the data in both
sheets is formatted the same way, and now I don't know what else to try.

it is not needed to coerce the second half of the formula, it is not a
logical statement unless it would help the equation recognise the value (I
don't know, but don't believe it does)

I agree it is a text versus number issue

try maybe

TotalR = Sheets("Imported Requisition Data").Evaluate("SUMPRODUCT(--(L9:L" &
myRowsR + 8 & "=" & OrderR & "),Value(Trim(G9:G" & myRowsR + 8 & "))")


"JLatham" wrote:

First thing I see in the first formula is that you're not coercing the second
half of the formula, try replacing
),(G9:G"...
with
),--(G9:G"...
although that really shouldn't affect the results the way you're using this.

As you noted, the format of the imported data appears to be a factor also -
the fact that it's showing up as "725" instead of just 725 indicates it's
stored as text instead of as a numeric value.

Try selecting all of the cells in that have the values shown with " "
surrounding them, and use Edit | Replace and select " as the character to
find and leave the replace with portion empty, click [Replace All]. I think
this is the one that's going to fix the problem.

"Helen" wrote:

Why would the result for TotalR = 0 (when it should return 226,550.00)
whereas TotalO returns the correct amount.

TotalR = Sheets("Imported Requisition Data").Evaluate("SUMPRODUCT(--(L9:L" &
myRowsR + 8 & "=" & OrderR & "),(G9:G" & myRowsR + 8 & "))")

TotalO = Sheets("JDE Data").Evaluate("SUMPRODUCT(--(B6:B" & myRowsO + 10

I've also noticed that results for other variables related to the ("Imported
Requisition Data") sheet appear as "725", whereas the results from ("JDE
Data") sheet comes through as 725.

I think this is part of the problem, but I've checked that the data in both
sheets is formatted the same way, and now I don't know what else to try.

If the other suggestions don't help, you may want to develop the formula that
works in that worksheet.

Then share that formula in your followup post.

I was gonna guess that OrderR is a number, but column L is text that looks like
a number.

.... + 8 & "=" & chr(34) & OrderR & chr(34) & ")....

(surround the OrderR in double quotes)

or maybe something like this:

.... + 8 & "=" & chr(34) & format(OrderR, "0000") & chr(34) & ")....

Helen wrote:

Why would the result for TotalR = 0 (when it should return 226,550.00)
whereas TotalO returns the correct amount.

TotalR = Sheets("Imported Requisition Data").Evaluate("SUMPRODUCT(--(L9:L" &
myRowsR + 8 & "=" & OrderR & "),(G9:G" & myRowsR + 8 & "))")

TotalO = Sheets("JDE Data").Evaluate("SUMPRODUCT(--(B6:B" & myRowsO + 10

I've also noticed that results for other variables related to the ("Imported
Requisition Data") sheet appear as "725", whereas the results from ("JDE
Data") sheet comes through as 725.

I think this is part of the problem, but I've checked that the data in both
sheets is formatted the same way, and now I don't know what else to try.

--

Dave Peterson