SUMIFS with currency selection

[Zubair]

HI Experts,

I have the following formula in my tab "Result" cell H7

=((SUMIFS(data!$S:$S,data!$Q:$Q,$A7,data!$B:$B,'Control Sheet'!$AH$2,data!$D:$D,'Control Sheet'!$AH$4)/1000/'Control Sheet'!$B$5))

The value coming from the above formula is in USD based on exchange rate from B5 whereas I want it to be calculate based on the selection drop down in tab "Control Sheet" B3 Local or USD,

means if local no division with B5 if USD divide with B5, Please help.

Select Month​	Apr
Select Year​	2021
View Currency​	Local	PKR​
Local Currency​	PKR
Exchange Rate​	156.6

 

[Alex Blakenburg]

You seem to have done the hard part. See if this is what you are after:-
PS: I am assuming that $A7 is on the same sheet as the formula.

=SUMIFS(Data!$S:$S,
Data!$Q:$Q,$A7,
Data!$B:$B,'Control Sheet'!$AH$2,
Data!$D:$D,'Control Sheet'!$AH$4)
/1000/IF('Control Sheet'!$B$3="Local",1,'Control Sheet'!$B$5)

 

[Zubair]

Alex - Exactly that I want, many thanks its perfectly work.

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.