SUMIFS help, calculate certain days revenue

[dmoore702]

I have a calendar setup where the columns are: Date, Day, Location A, Location B
The date is chronologial from the 1st to the end of the month, Day rotates down from M to Su, Location A & B are different amounts daily.
I can use SUMIF(C3:C33,"Su",D3:D33) and get the sum for all totals of Sundays during the month, here's where my difficulty starts.
I need to use two days, those being days off, so Su and M, or M and T, etc. I also need to repeat the formula for twelve months.
First month is columns A-D, second month G-J, third L-O etc.
Thanks in advance for the help.

 

[BigBeachBananas]

What do you mean by "I need to use two days"?

 

[dmoore702]

What do you mean by "I need to use two days"?

From the one column of days, I need the sum of both a Su and M or W and Th, a particular persons days off basically.
I could use two sumif formulas, one for the Sundays and one for the Mondays, but then have to repeat it for 12 months. Looking for a way to condense that formula

 

[awoohaw]

are days off always consecutive? Which version of excel do you use? ( Please update your profile with your version, many suggestions you may receive may not work on older versions of excel).

 

[awoohaw]

Here is a solution that has a lot of typing but just a few real calculations. It is using the reference area function of the INDEX function.
I am sure it can be done easier in later versions, and you may need to use the CSE (CNTL-SHFT-ENTR) keystrokes to commit the formula to the cell.
Also, you may need to switch the SUM function with SUMPRODUCT.

I've had to chop up the INDEX Ranges into different xl2bb minisheets:

Here is 1 of 4:

Mr excel questions 62.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		DATE	DAY	A	B		DATE	DAY	A	B		DATE	DAY	A	B		DATE	DAY	A	B
2		01-Jan	Sun	364.07	342.72		01-Feb	Wed	484.87	532.89		01-Mar	Wed	623.38	292.29		01-Apr	Sat	578.69	307.67
3		02-Jan	Mon	448.25	632.5		02-Feb	Thu	460.31	323.26		02-Mar	Thu	454.57	570.35		02-Apr	Sun	421.38	478.58
4		03-Jan	Tue	623.42	304.36		03-Feb	Fri	376.88	321.87		03-Mar	Fri	527.52	382.9		03-Apr	Mon	386.49	343.01
5		04-Jan	Wed	261.22	337.86		04-Feb	Sat	356.66	328.08		04-Mar	Sat	291.56	449.94		04-Apr	Tue	390.27	285.79
6		05-Jan	Thu	362.67	423.51		05-Feb	Sun	318.5	406.34		05-Mar	Sun	600.35	356.21		05-Apr	Wed	513.08	598.11
7		06-Jan	Fri	438.13	346.14		06-Feb	Mon	649.04	472.09		06-Mar	Mon	598.74	476.94		06-Apr	Thu	343.01	520.54
8		07-Jan	Sat	494.55	307.07		07-Feb	Tue	410.11	571.03		07-Mar	Tue	283.81	493.99		07-Apr	Fri	558.23	606.28
9		08-Jan	Sun	343.28	563.29		08-Feb	Wed	536.78	625.54		08-Mar	Wed	369.49	377.78		08-Apr	Sat	524.31	540.95
10		09-Jan	Mon	275.43	565.71		09-Feb	Thu	400.11	285.55		09-Mar	Thu	608.47	386.39		09-Apr	Sun	410.34	360.93
11		10-Jan	Tue	632.8	461.32		10-Feb	Fri	496.19	395.77		10-Mar	Fri	324.25	609.22		10-Apr	Mon	298.57	589.38
12		11-Jan	Wed	542.08	537.27		11-Feb	Sat	354.19	265.94		11-Mar	Sat	338.95	324.52		11-Apr	Tue	416.89	579.11
13		12-Jan	Thu	440.66	407.7		12-Feb	Sun	507.79	476.23		12-Mar	Sun	314.59	380.05		12-Apr	Wed	298.01	521.97
14		13-Jan	Fri	369.5	352.12		13-Feb	Mon	643.66	528.68		13-Mar	Mon	348.33	579.13		13-Apr	Thu	531.1	293.14
15		14-Jan	Sat	640.65	269.23		14-Feb	Tue	397.04	399.34		14-Mar	Tue	395.66	339.3		14-Apr	Fri	384.75	608.94
16		15-Jan	Sun	311.11	345.55		15-Feb	Wed	610.88	522.31		15-Mar	Wed	390.13	371.77		15-Apr	Sat	560.22	473.13
17		16-Jan	Mon	565.28	627.28		16-Feb	Thu	388.31	289.91		16-Mar	Thu	575.05	512.29		16-Apr	Sun	554.88	428.9
18		17-Jan	Tue	279.92	267.21		17-Feb	Fri	322.6	649.37		17-Mar	Fri	591.92	306.7		17-Apr	Mon	359.2	298.6
19		18-Jan	Wed	632.74	250.19		18-Feb	Sat	343.48	561.67		18-Mar	Sat	605.9	584.19		18-Apr	Tue	452.83	522.76
20		19-Jan	Thu	317.92	534.08		19-Feb	Sun	430.64	362.04		19-Mar	Sun	342.47	584.11		19-Apr	Wed	364.42	473.25
21		20-Jan	Fri	545.04	501.09		20-Feb	Mon	561.18	259.27		20-Mar	Mon	640.86	478.22		20-Apr	Thu	326.5	438.94
22		21-Jan	Sat	622.72	278.13		21-Feb	Tue	633.69	406.31		21-Mar	Tue	569.38	492.42		21-Apr	Fri	640.05	264.32
23		22-Jan	Sun	574.09	381.72		22-Feb	Wed	634.7	491.88		22-Mar	Wed	498.96	559.55		22-Apr	Sat	494.41	256.32
24		23-Jan	Mon	564.89	466.43		23-Feb	Thu	310.2	449.69		23-Mar	Thu	588.23	466.67		23-Apr	Sun	500.27	370.8
25		24-Jan	Tue	439.18	293.78		24-Feb	Fri	460.71	452.89		24-Mar	Fri	470.73	360.29		24-Apr	Mon	404.44	639.85
26		25-Jan	Wed	322.73	561.46		25-Feb	Sat	359.06	351.66		25-Mar	Sat	538.03	329.7		25-Apr	Tue	634.29	489.35
27		26-Jan	Thu	314.24	289.91		26-Feb	Sun	517.64	472.63		26-Mar	Sun	529.79	310.95		26-Apr	Wed	316.85	485.3
28		27-Jan	Fri	495.86	384.49		27-Feb	Mon	561.92	517.32		27-Mar	Mon	532.56	421.82		27-Apr	Thu	291.28	528.82
29		28-Jan	Sat	602.98	479.73		28-Feb	Tue	540.64	317.46		28-Mar	Tue	335.05	415.32		28-Apr	Fri	472.8	320.72
30		29-Jan	Sun	450.98	558.23							29-Mar	Wed	514.71	577.4		29-Apr	Sat	346.54	389.54
31		30-Jan	Mon	489.83	500.07							30-Mar	Thu	429.76	514.71		30-Apr	Sun	287.76	367.85
32		31-Jan	Tue	463.92	491.95							31-Mar	Fri	629.35	569.07
dmoore702

Here is 2 of 4:

Mr excel questions 62.xlsm
	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1	DATE	DAY	A	B		DATE	DAY	A	B		DATE	DAY	A	B		DATE	DAY	A	B
2	01-May	Mon	345.94	336.5		01-Jun	Thu	523.57	445.96		01-Jul	Sat	416.94	492.71		01-Aug	Tue	500.17	399.09
3	02-May	Tue	379.18	482.3		02-Jun	Fri	596.23	297.32		02-Jul	Sun	391.43	386.27		02-Aug	Wed	497.57	469.14
4	03-May	Wed	633.08	413.81		03-Jun	Sat	488.34	433.45		03-Jul	Mon	623.5	290		03-Aug	Thu	466.56	398.34
5	04-May	Thu	301.75	317.43		04-Jun	Sun	601.99	452.07		04-Jul	Tue	326.83	390.1		04-Aug	Fri	364.45	547.1
6	05-May	Fri	318.99	384.9		05-Jun	Mon	345.9	359.52		05-Jul	Wed	374.66	544.24		05-Aug	Sat	336.47	421.3
7	06-May	Sat	495.58	643.28		06-Jun	Tue	331.03	465.57		06-Jul	Thu	370.09	387.11		06-Aug	Sun	340.2	337.52
8	07-May	Sun	633.22	570.58		07-Jun	Wed	261.74	533.67		07-Jul	Fri	264.31	406.21		07-Aug	Mon	348.26	640.39
9	08-May	Mon	366.15	457.34		08-Jun	Thu	557.09	306.94		08-Jul	Sat	359.37	619.38		08-Aug	Tue	401.55	272.37
10	09-May	Tue	361.44	494.05		09-Jun	Fri	602.67	569.67		09-Jul	Sun	359.37	612.6		09-Aug	Wed	594.68	630.8
11	10-May	Wed	544.77	329.99		10-Jun	Sat	432.83	362.71		10-Jul	Mon	265.67	511.43		10-Aug	Thu	290.83	506.8
12	11-May	Thu	392.71	510.25		11-Jun	Sun	255.08	410.19		11-Jul	Tue	381.89	401.16		11-Aug	Fri	550.24	467.46
13	12-May	Fri	339.63	250.44		12-Jun	Mon	570.43	584.15		12-Jul	Wed	575.51	262.69		12-Aug	Sat	436.3	462.31
14	13-May	Sat	262.03	515.96		13-Jun	Tue	628.25	604.35		13-Jul	Thu	385.62	414.52		13-Aug	Sun	612.9	547.39
15	14-May	Sun	332.86	263.07		14-Jun	Wed	392.38	391.81		14-Jul	Fri	621.42	525.12		14-Aug	Mon	520.07	444.67
16	15-May	Mon	263.89	602		15-Jun	Thu	427.25	399.16		15-Jul	Sat	339.41	420.6		15-Aug	Tue	281.21	424.11
17	16-May	Tue	454.27	639.37		16-Jun	Fri	628.76	414.74		16-Jul	Sun	611.17	542.38		16-Aug	Wed	536.16	579.27
18	17-May	Wed	392.25	592.7		17-Jun	Sat	319.52	575.19		17-Jul	Mon	508.3	585.98		17-Aug	Thu	416.46	262.16
19	18-May	Thu	402.05	543.64		18-Jun	Sun	541.39	472.8		18-Jul	Tue	534.5	444.33		18-Aug	Fri	360.36	628.58
20	19-May	Fri	478.38	357.39		19-Jun	Mon	513.95	497.64		19-Jul	Wed	253.15	587.34		19-Aug	Sat	317.16	311.9
21	20-May	Sat	384.32	401.38		20-Jun	Tue	386.98	319.74		20-Jul	Thu	550.4	630.97		20-Aug	Sun	441.3	526.6
22	21-May	Sun	519.06	584.51		21-Jun	Wed	637.23	597.59		21-Jul	Fri	574.64	345.17		21-Aug	Mon	431.66	489.81
23	22-May	Mon	300.19	262.23		22-Jun	Thu	592.48	373.57		22-Jul	Sat	627.6	501.95		22-Aug	Tue	556.12	505.81
24	23-May	Tue	463.09	634.81		23-Jun	Fri	585.17	536.79		23-Jul	Sun	600.9	397.99		23-Aug	Wed	250.12	338.86
25	24-May	Wed	263.1	329.34		24-Jun	Sat	364.3	477.2		24-Jul	Mon	527.04	631.13		24-Aug	Thu	572.32	415.05
26	25-May	Thu	299.56	269.38		25-Jun	Sun	493.27	624.33		25-Jul	Tue	462.48	422.11		25-Aug	Fri	349.7	528.67
27	26-May	Fri	591.84	391.49		26-Jun	Mon	337.08	466.53		26-Jul	Wed	493.39	360.31		26-Aug	Sat	347.42	416.09
28	27-May	Sat	641.03	348.99		27-Jun	Tue	616.79	351.85		27-Jul	Thu	322.39	355.9		27-Aug	Sun	514.86	288.26
29	28-May	Sun	641.14	308.79		28-Jun	Wed	458.87	250.19		28-Jul	Fri	462.14	528.35		28-Aug	Mon	479.9	340.61
30	29-May	Mon	404.46	426.86		29-Jun	Thu	458.89	481.74		29-Jul	Sat	257.36	346.9		29-Aug	Tue	478.78	444.34
31	30-May	Tue	291.74	336.64		30-Jun	Fri	266.89	597.98		30-Jul	Sun	418.7	535.13		30-Aug	Wed	542.57	548.12
32	31-May	Wed	550.77	252.53							31-Jul	Mon	554.1	610.07		31-Aug	Thu	598.96	456.53
dmoore702

Here is 3 of 4:

Mr excel questions 62.xlsm
	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH
1	DATE	DAY	A	B		DATE	DAY	A	B		DATE	DAY	A	B		DATE	DAY	A	B
2	01-Sep	Fri	387.08	437.06		01-Oct	Sun	259.28	289.08		01-Nov	Wed	465.55	409.54		01-Dec	Fri	256.97	487
3	02-Sep	Sat	514.8	466.14		02-Oct	Mon	267.08	543.35		02-Nov	Thu	609.32	518.57		02-Dec	Sat	286.07	526.42
4	03-Sep	Sun	268.62	355.6		03-Oct	Tue	335.04	451.11		03-Nov	Fri	402.1	325.88		03-Dec	Sun	250.7	503.91
5	04-Sep	Mon	604.81	377.35		04-Oct	Wed	470.43	422.46		04-Nov	Sat	310.03	376.08		04-Dec	Mon	611.81	620.08
6	05-Sep	Tue	377.22	637.59		05-Oct	Thu	557.56	419.39		05-Nov	Sun	409.98	374.66		05-Dec	Tue	448.49	267.35
7	06-Sep	Wed	539.45	550.59		06-Oct	Fri	370.67	389.07		06-Nov	Mon	317.73	314.73		06-Dec	Wed	254.43	633.16
8	07-Sep	Thu	609.02	477.94		07-Oct	Sat	386.59	476.77		07-Nov	Tue	511.91	362.49		07-Dec	Thu	521.72	634.95
9	08-Sep	Fri	341	270.62		08-Oct	Sun	301.04	478.41		08-Nov	Wed	492.7	603.71		08-Dec	Fri	530.95	322.69
10	09-Sep	Sat	647.3	332.46		09-Oct	Mon	284.76	264.56		09-Nov	Thu	467.92	356.65		09-Dec	Sat	336.67	543.51
11	10-Sep	Sun	641.44	495.26		10-Oct	Tue	559.25	526.77		10-Nov	Fri	535.73	568.26		10-Dec	Sun	498.55	644.12
12	11-Sep	Mon	298.57	458.16		11-Oct	Wed	336.13	273.68		11-Nov	Sat	386.41	475.59		11-Dec	Mon	309.52	336.03
13	12-Sep	Tue	408.99	458.05		12-Oct	Thu	537.79	270.12		12-Nov	Sun	343.03	424.64		12-Dec	Tue	535.92	575.72
14	13-Sep	Wed	492.95	396.34		13-Oct	Fri	270.58	296.64		13-Nov	Mon	555.81	272.87		13-Dec	Wed	496.63	625.23
15	14-Sep	Thu	493.59	548.42		14-Oct	Sat	514.42	404.96		14-Nov	Tue	550.17	462.08		14-Dec	Thu	625.52	437.43
16	15-Sep	Fri	589.77	517.36		15-Oct	Sun	503.33	377.85		15-Nov	Wed	290.75	413.86		15-Dec	Fri	397.19	340.36
17	16-Sep	Sat	362.3	526.8		16-Oct	Mon	462.12	362.64		16-Nov	Thu	556.64	376.62		16-Dec	Sat	567.81	441.86
18	17-Sep	Sun	586.01	401.06		17-Oct	Tue	452.59	528.45		17-Nov	Fri	517.97	590.27		17-Dec	Sun	448.58	342.4
19	18-Sep	Mon	485.25	301.66		18-Oct	Wed	508.69	294.12		18-Nov	Sat	370.7	528.48		18-Dec	Mon	258.42	608.36
20	19-Sep	Tue	385.97	507.3		19-Oct	Thu	620.41	501.44		19-Nov	Sun	443.08	507.2		19-Dec	Tue	344.53	360.06
21	20-Sep	Wed	287.3	337.95		20-Oct	Fri	558.87	641.49		20-Nov	Mon	437.11	491.33		20-Dec	Wed	476.74	634.22
22	21-Sep	Thu	612.84	586.08		21-Oct	Sat	473.78	399.86		21-Nov	Tue	592.39	354.87		21-Dec	Thu	558.24	410.5
23	22-Sep	Fri	259.69	496.25		22-Oct	Sun	411.83	641.82		22-Nov	Wed	294.97	285.03		22-Dec	Fri	477.79	347.33
24	23-Sep	Sat	353.38	586.08		23-Oct	Mon	583.35	258.74		23-Nov	Thu	338.63	611.88		23-Dec	Sat	268.04	347.26
25	24-Sep	Sun	617.46	310.67		24-Oct	Tue	581.08	501.46		24-Nov	Fri	463.58	317.58		24-Dec	Sun	461.1	273.46
26	25-Sep	Mon	256.85	514.45		25-Oct	Wed	527.65	559.77		25-Nov	Sat	627.12	564.7		25-Dec	Mon	272.28	359.73
27	26-Sep	Tue	568.11	432.78		26-Oct	Thu	274.74	444.07		26-Nov	Sun	458.83	492.89		26-Dec	Tue	269.95	377.54
28	27-Sep	Wed	460.32	575.34		27-Oct	Fri	422.34	490.68		27-Nov	Mon	469.72	606.31		27-Dec	Wed	283.4	543.65
29	28-Sep	Thu	475.51	396.3		28-Oct	Sat	404.65	635.1		28-Nov	Tue	521.16	271		28-Dec	Thu	526.77	297.74
30	29-Sep	Fri	369.69	614.05		29-Oct	Sun	646.65	485.2		29-Nov	Wed	493.21	616.19		29-Dec	Fri	562.71	326.27
31	30-Sep	Sat	468.43	324.3		30-Oct	Mon	365.95	500.87		30-Nov	Thu	529.66	440.99		30-Dec	Sat	533.7	580.76
32						31-Oct	Tue	275.31	456.8							31-Dec	Sun	491.36	313.3
dmoore702

and 4 of 4, with the calculation:

Mr excel questions 62.xlsm
	A	B	C	D	E	F
34
35
36	Employee	Month	Day 1 Off	Day 2 Off	Sum Days Off in the Month
37	Name	Jan	Wed	Thu	6536.24
dmoore702

Cell Formulas
Range	Formula
E37	E37	=SUM( (--(WEEKDAY(INDEX(($B$2:$E$32,$G$2:$J$29,$L$2:$O$32,$Q$2:$T$31,$V$2:$Y$32,$AA$2:$AD$31,$AF$2:$AI$32,$AK$2:$AN$32,$AP$2:$AS$31,$AU$2:$AX$32,$AZ$2:$BC$31,$BE$2:$BH$32),0,1, MATCH(B37,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov,""Dec"},0)),2)=MATCH(C37:D37,{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0)) * INDEX(($B$2:$E$32,$G$2:$J$29,$L$2:$O$32,$Q$2:$T$31,$V$2:$Y$32,$AA$2:$AD$31,$AF$2:$AI$32,$AK$2:$AN$32,$AP$2:$AS$31,$AU$2:$AX$32,$AZ$2:$BC$31,$BE$2:$BH$32),0,3, MATCH(B37,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov,""Dec"},0))) + (--(WEEKDAY(INDEX(($B$2:$E$32,$G$2:$J$29,$L$2:$O$32,$Q$2:$T$31,$V$2:$Y$32,$AA$2:$AD$31,$AF$2:$AI$32,$AK$2:$AN$32,$AP$2:$AS$31,$AU$2:$AX$32,$AZ$2:$BC$31,$BE$2:$BH$32),0,1, MATCH(B37,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov,""Dec"},0)),2)=MATCH(C37:D37,{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0)) * INDEX(($B$2:$E$32,$G$2:$J$29,$L$2:$O$32,$Q$2:$T$31,$V$2:$Y$32,$AA$2:$AD$31,$AF$2:$AI$32,$AK$2:$AN$32,$AP$2:$AS$31,$AU$2:$AX$32,$AZ$2:$BC$31,$BE$2:$BH$32),0,4, MATCH(B37,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov,""Dec"},0))) )