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 somewhere:
=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 said:
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 said:
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
:
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 are:
$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_P92008_090808.xls]PAS
P9
wkst'!$A$2:$A$11818=B227),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$C$2:$C$11818=A227),'[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS
P9
wkst'!$P$2:$P$11818)*'[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P92008_090808.xls]PAS P9 wkst'!$M$2:$M$11818
:
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
:
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
:
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
:
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
20))