External

[sna]

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,

[puriwutpokin]

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)

[sna]

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.

[snasui]

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

[sna]

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


Best Regards,

[snasui]

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

[sna]

Thank you