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

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

If I understand you correctly, with the data given in A1:D5 try:

=SUMPRODUCT((B2:B5="GBP")*(C2:C5)*(D2:D5))

--
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

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