Modifying SUMIFS formula to a MODE formula??

[Jimmy05T]

Hello - I am looking to modify a SUMIFS formula I have in my worksheet to calculate the MODE of the range rather than sum the range. I have the set up as below and have a SUMIFS formula that sums all the values between two dates in a selected column. However, I would like to be able to identify the most common value in a range of dates in a chosen column. I would like to be able to have a column, start and end date inputs as per the SUMIFS formula. Is this possible? Please let me know if you require any further clarification?


A B C D E
1
2 Names
3 Data $B$7:$E$17
4 Dates $B$7:$B$17
5 Customers $B$7:$E$7
6
7 # A B
8 01/04/2017 3 1 2
9 02/04/2017 3 2 1
10 03/04/2017 7 4 3
11 04/04/2017 6 2 4
12 05/04/2017 6 2 4
13 06/04/2017 3 1 2
14 07/04/2017 3 2 1
15 08/04/2017 4 1 3
16 09/04/2017 4 2 2
17 10/04/2017 4 3 1
18
19 # 22 =SUMIFS(INDEX(Data,0,MATCH($B$19,Customers,0)),Dates,">="&$C$20,Dates,"<="&$C$21)
20 Start 03/04/2017
21 End 06/04/2017

 

[63falcondude]

Try this

Excel 2010
	A	B	C	D
8	4/1/2017	3	1	2
9	4/2/2017	3	2	1
10	4/3/2017	7	4	3
11	4/4/2017	6	2	4
12	4/5/2017	6	2	4
13	4/6/2017	3	1	2
14	4/7/2017	3	2	1
15	4/8/2017	4	1	3
16	4/9/2017	4	2	2
17	4/10/2017	4	3	1
18
19	Column	1
20	Start	4/3/2017
21	End	4/6/2017
22	Mode	6
Sheet1

Cell Formulas
Range	Formula
B22	{=MODE(IF((B20<=A8:A17)*(B21>=A8:A17),INDEX(B8:D17,,B19)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jimmy05T]

Thank you - this worked great. Would you know an easy way to amend the formula to exclude 0 values in the MODE calculation?

Try this

Excel 2010

	A	B	C	D
8	4/1/2017	3	1	2
9	4/2/2017	3	2	1
10	4/3/2017	7	4	3
11	4/4/2017	6	2	4
12	4/5/2017	6	2	4
13	4/6/2017	3	1	2
14	4/7/2017	3	2	1
15	4/8/2017	4	1	3
16	4/9/2017	4	2	2
17	4/10/2017	4	3	1
18
19	Column	1
20	Start	4/3/2017
21	End	4/6/2017
22	Mode	6

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B22 {=MODE(IF((B20<=A8:A17)*(B21>=A8:A17),INDEX(B8:D17,,B19)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[63falcondude]

Thank you - this worked great. Would you know an easy way to amend the formula to exclude 0 values in the MODE calculation?

Try including *(B8:D17<>0) into the formula. The formula would then become:

=MODE(IF((B20<=A8:A17)*(B21>=A8:A17)*(B8:D17<>0),INDEX(B8:D17,,B19))) Ctrl Shift Enter

 

[Jimmy05T]

Thank you - however, what if the whole row isn't 0, just a data point in say column 2? But other columns do have values. Is there a way it can just exclude the 0 values when calculating the MODE for that column? (but still keeping the column selection dynamic as part of the formula)

Thank you for your help and apologies for not being clear.

Try including *(B8:D17<>0) into the formula. The formula would then become:

=MODE(IF((B20<=A8:A17)*(B21>=A8:A17)*(B8:D17<>0),INDEX(B8:D17,,B19))) Ctrl Shift Enter

 

[63falcondude]

If I am understanding you correctly it should work. Did you try it? In the example below, the mode (in the first column between the date range) would normally be 0 but the formula ignores that and returns 7.

Excel 2010
	A	B	C	D
8	4/1/2017	3	1	2
9	4/2/2017	3	2	1
10	4/3/2017	7	4	3
11	4/4/2017	7	2	4
12	4/5/2017	0	2	4
13	4/6/2017	0	1	2
14	4/7/2017	0	2	1
15	4/8/2017	4	1	3
16	4/9/2017	4	2	2
17	4/10/2017	4	3	1
18
19	Column	1
20	Start	4/3/2017
21	End	4/7/2017
22	Mode	7
Sheet1

Cell Formulas
Range	Formula
B22	{=MODE(IF((B20<=A8:A17)*(B21>=A8:A17)*(B8:D17<>0),INDEX(B8:D17,,B19)))}
Press CTRL+SHIFT+ENTER to enter array formulas.