Query on sumif

[senthilkumar_rm]

Region Customer Name Sales

East Mahesh 3300

East Shahul 2300

East Prabakar 1400

East Samsu 2345

East Perumal 1234

West Mahesh 889.7

West Shahul 481

West Prabakar 72.3

West Samsu 7300

North Perumal 2344

North Mahesh 1323

North Shahul 6777

North Prabakar 6554

North Samsu 7773

South Perumal 8777

South Mahesh 9781

South Shahul 10785

South Prabakar 11789

South Samsu 12793


I need to display which region has lesser sales (answer : West)

I have tried all possibilities of sumif and ifs


but unable to solve

 

[Krishnakumar]

Create a pivot table and Sort the sales by Ascending order.


Kris

 

[sdsurzh]

Hi,


Assume your data from A to C column.


On E Column paste region (East,west,north & south) & F column paste the below formula and drag

=SUMIFS($C$2:$C$20,$A$2:$A$20,$E2)


Region Sales

East 10579

west 8743

North 24771

South 53925


Thanks,

Suresh Kumar S

 

[Debraj]

Hi Senthil,


Can you please try the below for me..

{=INDEX($A$2:$A$20,MATCH(SMALL(SUMIF($A$2:$A$20,A2:A20,$C$2:$C$20),1),SUMIF($A$2:$A$20,A2:A20,$C$2:$C$20),0))}

use Ctrl + Shift + Enter to confirm the formula..


PS: Using Name it looks like..

{=INDEX(Region,MATCH(SMALL(SUMIF(Region,Region,Sales),1),SUMIF(Region,Region,Sales),0))}

Regards,

Deb

 

[sdsurzh]

Thanks Debraj Roy (I Gained a new formula)

 

[anupamtiwari05]

Hi Deb,


You are genius, thanks a lot for such a nice formula.


Regards,

Anupam

 

[majay1973]

Hi Deb,


That was great !!!