Snasui Excel Forum

Hi
I need your help to find how many loan refinance greater than 5000$ and it's amount?
how many loan refinance less than or equal 5000$ and it's amount for both branch KPI and STG in Sheet4?

Best Regards,

I don't know, do I get it right? If yes then follow this Can be viewed from the attachment
Key according to this
F1=USD,G1=KPI,H1=STG
F2=>5000
F3=<=500
G2:H3=SUMIFS($C$2:$C$11,$A$2:$A$11,G$1,$C$2:$C$11,$F2,$B$2:$B$11,$F$1)

Thanks but I have three currencies.this is just for sample a two-currency transactions.I need both amount and number means I need sum and count.

Could you please enter your correct results and attach file again?

The expected result also provided in the template.is it possible to do with pivot table???


Best Regards,

Try this,

1. E2 *** If you have the exchange rate that needed to convert, you should create the converted column for easy to manage. ***
   =IF(B2="KHR",D2/4000,D2)
   Enter > Copy down
2. G2
   =SUMIFS($E$2:$E$11,$B$2:$B$11,$F$1,$A$2:$A$11,$G$1,$E$2:$E$11,$F2)
   Enter > Copy down
3. H2
   =COUNTIFS($B$2:$B$11,$F$1,$A$2:$A$11,$G$1,$E$2:$E$11,$F2,$E$2:$E$11,">0")
   Enter > Copy down
4. I2
   =SUMIFS($E$2:$E$11,$B$2:$B$11,$F$1,$A$2:$A$11,$I$1,$D$2:$D$11,$F2)
   Enter > Copy down
5. J2
   =COUNTIFS($B$2:$B$11,$F$1,$A$2:$A$11,$I$1,$E$2:$E$11,$F2,$E$2:$E$11,">0")
   Enter > Copy down
6. G7
   =SUMIFS($E$2:$E$11,$B$2:$B$11,$F$6,$A$2:$A$11,$G$6,$E$2:$E$11,$F7)
   Enter > Copy down
7. H7
   =COUNTIFS($B$2:$B$11,$F$6,$A$2:$A$11,$G$6,$E$2:$E$11,$F7,$E$2:$E$11,">0")
   Enter > Copy down
8. I7
   =SUMIFS($E$2:$E$11,$B$2:$B$11,$F$6,$A$2:$A$11,$I$6,$E$2:$E$11,$F7)
   Enter > Copy down
9. J7
   =COUNTIFS($B$2:$B$11,$F$6,$A$2:$A$11,$I$6,$E$2:$E$11,$F7,$E$2:$E$11,">0")
   Enter > Copy down

Thank you