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

Try

=sumproduct(--(a1:a100="Location"),--(b1:b100="Product),(c1:c100)/(sumproduct(--(a1:a100="Location"),--(b1:b100="Product),(c1:c100)
+ sumproduct(--(a1:a100="Location"),--(b1:b100="Product),(d1:d100))

No error checking provided.

"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

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

=SUM(IF((A2:A20="Location")*(B2:B20="Product"),C2: C20/D2:D20))

this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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

Hello again,
I have had computer problems. I will re-check with the formula's that you
all have submitted. Thanks for your help for now. I will get back to you
all.

Rich

"Bob Phillips" wrote:

=SUM(IF((A2:A20="Location")*(B2:B20="Product"),C2: C20/D2:D20))

this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

--
__________________________________
HTH

Bob

"MrRJ" wrote in message
...
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

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

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

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

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

Oh, and also to clarify, you are actually wanting the values in column P
multiplied by column M, according to your formula, is that also correct?
--
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

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

Yes, in a sense that is what columns C & D I was stating below is.

"John C" wrote:

Oh, and also to clarify, you are actually wanting the values in column P
multiplied by column M, according to your formula, is that also correct?
--
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

I guess I don't see why you are referring to columns C & D, as it appears
that that information you are still pulling from the other workbook.

I agree with what should be calculated based on the data you gave, but I am
guessing that there is another portion of data that is matching your
criteria. Perhaps in your original workbook, create a temporary help column
to find the trouble, i.e.:
in a helper cell in row 2 somewhe
=IF(AND(A2="Spokane",C2="Cans"),P2*M2,"")
Then you can autofilter on the helper column, and look for values 0, and
you can then determine if there is some data that it is calculating that it
shouldn't be.

--
John C


"MrRJ" wrote:

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

It is funny that you mentioned that. I have already did that "filter" thing
that you requested. I did that on my source file. That is how I got my
number that I should be coming too with the sumproduct formula that we are
working on.
Don't get hung up on the C & D thiing, it was for example. The real columns
used are P & M.

I must be going blind, because I cannot figure out why it is not working.

"John C" wrote:

I guess I don't see why you are referring to columns C & D, as it appears
that that information you are still pulling from the other workbook.

I agree with what should be calculated based on the data you gave, but I am
guessing that there is another portion of data that is matching your
criteria. Perhaps in your original workbook, create a temporary help column
to find the trouble, i.e.:
in a helper cell in row 2 somewhe
=IF(AND(A2="Spokane",C2="Cans"),P2*M2,"")
Then you can autofilter on the helper column, and look for values 0, and
you can then determine if there is some data that it is calculating that it
shouldn't be.

--
John C


"MrRJ" wrote:

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

Hey John,
I found it. I just add the -- marks at the beginning of the multiplication
pair. Whew, that was close. Thanks for your help.

"John C" wrote:

I guess I don't see why you are referring to columns C & D, as it appears
that that information you are still pulling from the other workbook.

I agree with what should be calculated based on the data you gave, but I am
guessing that there is another portion of data that is matching your
criteria. Perhaps in your original workbook, create a temporary help column
to find the trouble, i.e.:
in a helper cell in row 2 somewhe
=IF(AND(A2="Spokane",C2="Cans"),P2*M2,"")
Then you can autofilter on the helper column, and look for values 0, and
you can then determine if there is some data that it is calculating that it
shouldn't be.

--
John C


"MrRJ" wrote:

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