SUMIFS for different criteria, without criteria_range in database

[Deepakchoudhary]

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

<TBODY>
</TBODY>

 

[Jonmo1]

Try

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

 

[Oeldere]

I would use pivot table for that kind of work.

Book1
	H	I	J	K	L
2	Som van TurnOver	Kolomlabels
3	Rijlabels	42014	52014	62014	Eindtotaal
4	Mr X	4,39	4,67	4,71	13,77
5	7	0,37	0,40	0,42	1,19
6	8	0,31	0,37	0,36	1,04
7	9	0,38	0,40	0,44	1,22
8	10	0,60	0,60	0,61	1,81
9	11	0,30	0,32	0,34	0,96
10	12	0,26	0,27	0,28	0,81
11	13	0,87	0,94	0,95	2,76
12	25	0,37	0,38	0,40	1,15
13	61	0,61	0,64	0,55	1,80
14	99	0,32	0,35	0,36	1,03
15	Mr Y	2,88	3,55	3,07	9,50
16	14	0,47	0,52	0,53	1,52
17	15	0,25	0,28	0,32	0,85
18	17	0,50	0,78	0,57	1,85
19	18	0,69	0,85	0,62	2,16
20	19	0,23	0,34	0,23	0,80
21	20	0,49	0,50	0,53	1,52
22	21	0,25	0,28	0,27	0,80
23	Mr Z	4,45	4,65	4,39	13,49
24	56	0,43	0,40	0,44	1,27
25	59	0,49	0,49	0,50	1,48
26	70	0,44	0,48	0,50	1,42
27	88	0,32	0,33	0,34	0,99
28	92	1,17	1,28	1,14	3,59
29	93	0,30	0,57	0,36	1,23
30	96	0,65	0,67	0,63	1,95
31	97	0,65	0,43	0,48	1,56
32	Eindtotaal	11,72	12,87	12,17	36,76
Blad12

 

[Deepakchoudhary]

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

 

[Jonmo1]

You're welcome.