If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculation on SUMIF Results
Hi all,
I have a bit of a proble, I want to perform a rate calculation on the results of a sumif i.e. Customer Currency Rate value 1 USD 1 500 2 GBP .7 700 3 GBP .71 750 4 GBP .72 700 So I want to get the native currency for all GBP paying customers but the rates are different so I cant just divide the rate at the end, it needs to be done on the fly. This problem is on a standard report so I can't just convert all the values on a separate column and do the sumif on the results. Any Ideas would be v helpful |
#2
|
|||
|
|||
On Sat, 8 Oct 2005 04:08:02 -0700, Andy B
wrote: Hi all, I have a bit of a proble, I want to perform a rate calculation on the results of a sumif i.e. Customer Currency Rate value 1 USD 1 500 2 GBP .7 700 3 GBP .71 750 4 GBP .72 700 So I want to get the native currency for all GBP paying customers but the rates are different so I cant just divide the rate at the end, it needs to be done on the fly. This problem is on a standard report so I can't just convert all the values on a separate column and do the sumif on the results. Any Ideas would be v helpful I'm not sure exactly what you are trying to do, but perhaps SUMPRODUCT would work: =SUMPRODUCT(rate,value) or perhaps =SUMPRODUCT(1/rate,value) --ron |
#3
|
|||
|
|||
If I understand you correctly, with the data given in A15 try:
=SUMPRODUCT((B2:B5="GBP")*(C2:C5)*(D25)) -- HTH Sandy Replace@mailinator with @tiscali.co.uk "Andy B" wrote in message ... Hi all, I have a bit of a proble, I want to perform a rate calculation on the results of a sumif i.e. Customer Currency Rate value 1 USD 1 500 2 GBP .7 700 3 GBP .71 750 4 GBP .72 700 So I want to get the native currency for all GBP paying customers but the rates are different so I cant just divide the rate at the end, it needs to be done on the fly. This problem is on a standard report so I can't just convert all the values on a separate column and do the sumif on the results. Any Ideas would be v helpful |
#4
|
|||
|
|||
Hi,
Here is another way to do it using sumproduct and an array formula. I would still advise ehat you use Sandy's solution. Nevertheless i am proposing this so that you can know about array formulas Press Ctrl+Shift+Enter =SUMPRODUCT(IF($E$4:$E$6="GBP",F4:F6*G4:G6)) F4:F6 - rate column G4:G6 - Value column Regards, Ashish Mathur "Andy B" wrote: Hi all, I have a bit of a proble, I want to perform a rate calculation on the results of a sumif i.e. Customer Currency Rate value 1 USD 1 500 2 GBP .7 700 3 GBP .71 750 4 GBP .72 700 So I want to get the native currency for all GBP paying customers but the rates are different so I cant just divide the rate at the end, it needs to be done on the fly. This problem is on a standard report so I can't just convert all the values on a separate column and do the sumif on the results. Any Ideas would be v helpful |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to group and count results of calculation | shep | Running & Setting Up Queries | 12 | June 27th, 2006 11:50 AM |
Variable for holding a calculation for a text box | patentinv | General Discussion | 3 | October 9th, 2005 01:23 PM |
SUMIF function in "Price quote with tax calculation" templae | Peter | Worksheet Functions | 6 | October 3rd, 2005 07:18 PM |
Equity buildup calculation | JimDandy | Worksheet Functions | 5 | September 6th, 2005 05:57 PM |
How to use SUMIF to return sums between two values located in cells | ScottBerger | Worksheet Functions | 1 | November 18th, 2004 07:09 PM |