John,
I am using another spreadsheet as my source. Therefore, my ranges are
comming from my other spreadsheets and the values I need to find are in the
current spreadsheet. Hence, it is not the same.

In searching my range A from another spreadsheet, I am looking for Spokane,
which is labeled in my current file. Same thing for range C, which I was
looking for specific product, such as Cans, which is labeled in my current
file.

Below are the values that are the result of my ranges in A & B. now, I need
to compute. Do you agree that I should compute to 32,717.21?

Yes, all my workbooks are open.

"John C" wrote:

Well, when I take the values you gave, and use SUMPRODUCT on them, it
calculates just fine. Your formula looks fine, but I do have one question
regarding it.
Your first portion is checking the range $a$2:$a$11818, and seeing if it is
equal to a value in cell B227, but your second portion is checking the range
in $c$2:$c$11818, and checking to see if it is equal to the value in
A227(which is part of the first range). Is this correct?
Also, I am assuming that the other workbook is open when this calculation is
done.
--
John C


"MrRJ" wrote:

Hi John,

Take a look what I have. The number given is incorrect. What did I do wrong.

The values for column C is
166.250
83.120
124.680
249.370
249.370
124.680
290.930
166.250
207.810

The values for column D a
$19.68
$19.68
$19.68
$19.68
$19.68
$19.68
$19.68
$19.68
$19.68

Using this formula, I get the value of 88,648.56. I should have 32,717.21.
Basically, I need the sum of C x D. Does that make sense?

=SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS
P9
wkst'!$A$2:$A$11818=B227),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS
P9
wkst'!$C$2:$C$11818=A227),'[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS
P9
wkst'!$P$2:$P$11818)*'[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS P9 wkst'!$M$2:$M$11818


"John C" wrote:

Assuming I understand you correctly....

=SUMPRODUCT(--($A$2:$A$100=location),--($B$2:$B$100=product),($C$2:$C$100)*($D$2:$D$100))

Hope this helps.
--
John C


"MrRJ" wrote:

John,
sorry about that. Here is further clarification. Perhaps I can state it
this way.

I need the sum of volume x rate. This is based on 2 criteria I set for
location and Product.

Location Product Volume Rate

Hope this clears it?
Rich



"John C" wrote:

Perhaps further clarification on your formula is needed. Sum of volume * (Sum
of Amount / Sum of Volume) will be equal to Sum of Amount.
--
John C


"MrRJ" wrote:

Good morning!

I am using Sumproduct in my spreadsheet. I am trying to do a division
within. Here is my problem.

Location Product volumn Amount

I need to match the location and product, once that is done, then I need to
do sum of volume * (sum of amount / sum of volume).

Hope this makes sense.
Thanks,
Rich