SUMIF OFFSET 1 column

[lost_in_the_sauce]

I hope this is easy, I can post the xl2bb if needed. My sheet has forecast and actuals side by side with the date above the forecast value each month.

=SUMIF(A:A,C5,D:D)

When it returns the value from D (example, C5 matches A13 so D13 is returned) - I would like to take one cell to the right instead to capture the actual numbers and not the forecast (cell E14 in this example.)

 

[SanjayGMusafir]

Post XL2BB, I think it can be done some other way...

 

[lost_in_the_sauce]

Post XL2BB, I think it can be done some other way...

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1		Jan-23			Feb-23			Mar-23			Apr-23			May-23			Jun-23			Jul-23			Aug-23			Sep-23
2		Plan	Act	∆	Plan	Act	∆	Plan	Act	∆	Plan	Act	∆	Plan	Act	∆	Plan	Act	∆	Plan	Act	∆	Plan	Act	∆	Plan	Act	∆
3	Royalty	772	824	52	769	793	24	725	707	-18	820	809	-11	771	742	-29	813	850	37	810	754	-56	747	763	16	795	847	52
4	eCommerce	105	118	13	140	104	-36	108	136	28	132	121	-11	122	112	-10	121	141	20	150	106	-44	146	149	3	109	108	-1
5	Merch	88	81	-7	95	95	0	97	84	-13	99	99	0	91	89	-2	99	82	-17	91	87	-4	80	98	18	96	85	-11
6	Rebates	36	36	0	33	40	7	31	38	7	30	37	7	32	38	6	37	36	-1	34	37	3	39	32	-7	33	30	-3
7	Revenue	1001	1059	58	1037	1032	-5	961	965	4	1081	1066	-15	1016	981	-35	1070	1109	39	1085	984	-101	1012	1042	30	1033	1070	37
8
9
10		1/31/2023	2/28/2023	3/31/2023	4/30/2023	5/31/2023	6/30/2023	7/31/2023	8/31/2023	9/30/2023
11	Actual Revenue	1001	1037	961	1081	1016	1070	1085	1012	1033
Sheet1

Cell Formulas
Range	Formula
E1,Z1,W1,T1,Q1,N1,K1,H1	E1	=EOMONTH(B1,1)
B7:AB7	B7	=SUM(B3:B6)
B11:J11	B11	=SUMIF(1:1,B10,7:7)

 

[lost_in_the_sauce]

The formulas in B11:J11 are returning the Plan number, is there a way to have the output be the Act number, or one cell to the left?

 

[SanjayGMusafir]

I did a little bit of trick in Row 1 to get the desired results in Row 12. Check C1, D1, F1, G1...

See if it works for you...

Also I have changed Row 10 to standardise Data.

This was the best I could think without changing the structure of your data...

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1		Jan-2023	Jan-2023	Jan-2023	Feb-2023	Feb-2023	Feb-2023	Mar-2023	Mar-2023	Mar-2023	Apr-2023	Apr-2023	Apr-2023	May-2023	May-2023	May-2023	Jun-2023	Jun-2023	Jun-2023	Jul-2023	Jul-2023	Jul-2023	Aug-2023	Aug-2023	Aug-2023	Sep-2023	Sep-2023	Sep-2023
2		Plan	Act	∆	Plan	Act	∆	Plan	Act	∆	Plan	Act	∆	Plan	Act	∆	Plan	Act	∆	Plan	Act	∆	Plan	Act	∆	Plan	Act	∆
3	Royalty	772	824	52	769	793	24	725	707	-18	820	809	-11	771	742	-29	813	850	37	810	754	-56	747	763	16	795	847	52
4	eCommerce	105	118	13	140	104	-36	108	136	28	132	121	-11	122	112	-10	121	141	20	150	106	-44	146	149	3	109	108	-1
5	Merch	88	81	-7	95	95	0	97	84	-13	99	99	0	91	89	-2	99	82	-17	91	87	-4	80	98	18	96	85	-11
6	Rebates	36	36	0	33	40	7	31	38	7	30	37	7	32	38	6	37	36	-1	34	37	3	39	32	-7	33	30	-3
7	Revenue	1001	1059	58	1037	1032	-5	961	965	4	1081	1066	-15	1016	981	-35	1070	1109	39	1085	984	-101	1012	1042	30	1033	1070	37
8
9
10		Jan-2023	Feb-2023	Mar-2023	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023
11	Planned	1001	1037	961	1081	1016	1070	1085	1012	1033
12	Act	1059	1032	965	1066	981	1109	984	1042	1070
Sheet1

Cell Formulas
Range	Formula
C1,F1,I1,L1,O1,R1,U1,X1,AA1	C1	=B1
D1,G1,J1,M1,P1,S1,V1,Y1,AB1	D1	=B1
E1,H1,K1,N1,Q1,T1,W1,Z1	E1	=EOMONTH(B1,1)
B7:AB7	B7	=SUM(B3:B6)
C10:J10	C10	=EOMONTH(B10,1)
B12:J12	B12	=SUMIFS(7:7,1:1,B10,2:2,$A$12)