Why it is reflecting Zero ?

[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


This is above data in column A , B, C respectively

When i used sumifs formula as


=SUMIFS(sales,region,"east",region, "west",region,"north",region,"south")


It is reflecting as zero instead of total sum of sales


Why ?

 

[keymaster]

The conditions of SUMIFS must all be met. Since in your data, no row is in East, West, South and North regions it gives zero. To get what you want, use this instead:


=SUMIFS(sales,region,"east") + SUMIFS(sales,region,"west") + SUMIFS(sales,region,"north") + SUMIFS(sales,region,"south")

 

[senthilkumar_rm]

Thanks !! chandoo


Now i understood

 

[Debraj]

Hi Senthil..


As you have already guided by KEY MASTER.. now you can use the below formula also..

{=SUM(IF(($A$2:$A$20="East")+($A$2:$A$20="South")+N("Increase if more required..."),$C$2:$C$20))}

Regards,

Deb