# SUMIFS for different criteria, without criteria_range in database

#### Deepakchoudhary

##### New Member
Friends,
in below database 3 columns are exported from S/W (i.e. Calendar M/Y, Outlet Code, Turnover) showing turnover of 3 months for different outlets belongs to 3 city managers Mr X, Mr Y & Mr Z.
Here City Manager's column is not available in exported data, just listed below for reference. I need to make SUMIFS of Turnover for the City Manager(s) on the basis of their belonging OUTLET CODES. For example Mr X is looking for the OUTLET CODEs from 7 to 13 & 25,61,99. Chellange was "City Manager" column not available to set the 'crieteria range' to make sumifs condition. Applied a working formula as below :
(\$C:\$C is Turnover, \$B:\$B is Outlet Code)

=SUMIFS(\$C:\$C,\$B:\$B,">="&"7",\$B:\$B,"<="&"13")+SUMIFS(\$C:\$C,\$B:\$B,"25")+SUMIFS(\$C:\$C,\$B:\$B,"61")+SUMIFS(\$C:\$C,\$B:\$B,"99")
and correct result is 13.766. (After "+" SUMIF is also working well).

I need help for any other easy solution if possible. Again and again writing "+" & fromula needs some other way if anyone can give me. Already tried for
1. =SUMIFS(\$C:\$C,\$B:\$B,">="&"7",\$B:\$B,"<="&"13")+SUMIFS(\$C:\$C,\$B:\$B,"25",\$B:\$B,"61",\$B:\$B,"99")
2. =SUMIFS(\$C:\$C,\$B:\$B,">="&"7",\$B:\$B,"<="&"13",\$B:\$B,"25",\$B:\$B,"61",\$B:\$B,"99")
But these are not working.

 CALENDAR M/Y OUTLET CODE TurnOver City Manager 4.2014 7 0.37 Mr X 4.2014 8 0.31 Mr X 4.2014 9 0.38 Mr X 4.2014 10 0.60 Mr X 4.2014 11 0.30 Mr X 4.2014 12 0.26 Mr X 4.2014 13 0.87 Mr X 4.2014 14 0.47 Mr Y 4.2014 15 0.25 Mr Y 4.2014 17 0.50 Mr Y 4.2014 18 0.69 Mr Y 4.2014 19 0.23 Mr Y 4.2014 20 0.49 Mr Y 4.2014 21 0.25 Mr Y 4.2014 56 0.43 Mr Z 4.2014 59 0.49 Mr Z 4.2014 25 0.37 Mr X 4.2014 70 0.44 Mr Z 4.2014 88 0.32 Mr Z 4.2014 61 0.61 Mr X 4.2014 92 1.17 Mr Z 4.2014 93 0.30 Mr Z 4.2014 96 0.65 Mr Z 4.2014 97 0.65 Mr Z 4.2014 99 0.32 Mr X 5.2014 7 0.40 Mr X 5.2014 8 0.37 Mr X 5.2014 9 0.40 Mr X 5.2014 10 0.60 Mr X 5.2014 11 0.32 Mr X 5.2014 12 0.27 Mr X 5.2014 13 0.94 Mr X 5.2014 14 0.52 Mr Y 5.2014 15 0.28 Mr Y 5.2014 17 0.78 Mr Y 5.2014 18 0.85 Mr Y 5.2014 19 0.34 Mr Y 5.2014 20 0.50 Mr Y 5.2014 21 0.28 Mr Y 5.2014 56 0.40 Mr Z 5.2014 59 0.49 Mr Z 5.2014 25 0.38 Mr X 5.2014 70 0.48 Mr Z 5.2014 88 0.33 Mr Z 5.2014 61 0.64 Mr X 5.2014 92 1.28 Mr Z 5.2014 93 0.57 Mr Z 5.2014 96 0.67 Mr Z 5.2014 97 0.43 Mr Z 5.2014 99 0.35 Mr X 6.2014 7 0.42 Mr X 6.2014 8 0.36 Mr X 6.2014 9 0.44 Mr X 6.2014 10 0.61 Mr X 6.2014 11 0.34 Mr X 6.2014 12 0.28 Mr X 6.2014 13 0.95 Mr X 6.2014 14 0.53 Mr Y 6.2014 15 0.32 Mr Y 6.2014 17 0.57 Mr Y 6.2014 18 0.62 Mr Y 6.2014 19 0.23 Mr Y 6.2014 20 0.53 Mr Y 6.2014 21 0.27 Mr Y 6.2014 56 0.44 Mr Z 6.2014 59 0.50 Mr Z 6.2014 25 0.40 Mr X 6.2014 70 0.50 Mr Z 6.2014 88 0.34 Mr Z 6.2014 61 0.55 Mr X 6.2014 92 1.14 Mr Z 6.2014 93 0.36 Mr Z 6.2014 96 0.63 Mr Z 6.2014 97 0.48 Mr Z 6.2014 99 0.36 Mr X

<TBODY>
</TBODY>

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
Try

=SUM(SUMIF(\$B:\$B,{7,8,9,10,11,12,13,25,61,99},\$C:\$C))

I would use pivot table for that kind of work.

Book1
HIJKL
2Som van TurnOverKolomlabels
3Rijlabels420145201462014Eindtotaal
4Mr X4,394,674,7113,77
570,370,400,421,19
680,310,370,361,04
790,380,400,441,22
8100,600,600,611,81
9110,300,320,340,96
10120,260,270,280,81
11130,870,940,952,76
12250,370,380,401,15
13610,610,640,551,80
14990,320,350,361,03
15Mr Y2,883,553,079,50
16140,470,520,531,52
17150,250,280,320,85
18170,500,780,571,85
19180,690,850,622,16
20190,230,340,230,80
21200,490,500,531,52
22210,250,280,270,80
23Mr Z4,454,654,3913,49
24560,430,400,441,27
25590,490,490,501,48
26700,440,480,501,42
27880,320,330,340,99
28921,171,281,143,59
29930,300,570,361,23
30960,650,670,631,95
31970,650,430,481,56
32Eindtotaal11,7212,8712,1736,76

Thanks bro, it is working. It is really very small to put for desired result. Thank a lot.

You're welcome.

Replies
3
Views
221
Replies
6
Views
257
Replies
15
Views
400
Replies
3
Views
391
Replies
0
Views
462

1,203,462
Messages
6,055,563
Members
444,799
Latest member
CraigCrowhurst

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back