Help SUMIF with YTD

[shepcs]

Would greatly appreciate any help as its driving me nuts.
I'm trying to do a YTD summation given a value to lookup in a single column (Column C), then doing a ytd in the Jan-Dec columns (E:P).
So for Inputs I have a number to lookup in a column and a date (in this case E2: 400101 and E3: 05/31/22)

For a single value its no problem, I do
=SUMIF($C:$C,E3,INDEX($E:$P,,MATCH(E2,$E$6:$P$6,0)))

And I know the YTD formula works but this is inputting a specific row (E12:P12) instead of location the row by the input number E2
=SUMIF($E$6:$P$6,"<="&E2,E12:P12)

I'm trying to combine the two and running into a problem.

Really appreciate any insight.
Thanks!!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMPRODUCT(($C11:$C500=E3)*($E$6:$P$6<=E2)*(E11:P500))

 

[shepcs]

Just updated my info thanks. But that formula doesnt work.

 

[Fluff]

Just updated my info

Thanks for that.

In what way doesn't the formula work?

 

[shepcs]

It just gives me a value of zero instead of the (1269521)

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jdellasala]

Now that we know you have 365, this will be relevant!

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Input	Month	YTD
2	Month:	Mar-22	15,224
3	Dept:	400100
4
5	Dept	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
6	400100	7,256	2,147	5,821	7,742	3,358	4,298	1,491	9,534	9,378	3,427	4,496	8,370
7	400110	9,646	5,341	6,714	9,356	8,951	8,236	2,011	4,421	9,849	9,689	2,559	7,365
8	400120	6,005	6,014	4,955	5,034	3,030	4,226	5,027	7,237	1,373	5,763	8,373	1,132
9	400130	3,391	8,896	8,046	6,721	4,619	9,569	4,969	8,125	7,137	3,395	2,793	6,875
10	400140	5,705	7,559	2,926	5,456	5,158	9,725	8,868	8,000	9,789	1,700	4,474	7,823
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUM(FILTER(FILTER(C6:N10,(MONTH(C5#)<=MONTH(C2))),B6#=C3))
C5:N5	C5	=EOMONTH(DATEVALUE("1/1/2022"),SEQUENCE(,12,0))
B6:B10	B6	=SEQUENCE(5,,400100,10)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$C$5#
C3	List	=$B$6#

And yes, would be helpful to use XL2BB instead of having to make it up!

 

[shepcs]

Thanks jdellasala! I'm still trying to digest what you gave me. And will do on the XL2BB. I appreciate it!

 

[shepcs]

I guess I'm still doing something wrong. I tried tweaking it but couldnt get it to work regardless. I got xl2bb to work

SUMIF HELP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	RAW Data
3	DEPT	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	2022
4	400100	9,866,446	6,198,637	6,198,637	6,198,637	11,768,274	14,485,170	44,778,560	-	-	-	-	-	99,494,360
5	400101	(587,340)	(388,978)	(242,392)	(17,025)	(33,785)	(55,538)	(134,922)	-	-	-	-	-	(1,459,981)
6	400160	372,034	372,034	372,034	372,034	372,034	372,034	372,034	7,378,534	7,378,534	7,378,534	5,292,577	5,292,577	35,324,994
7	400170	-	-	-	-	-	-	-	-	-	-	-	-	-
8	400171	-	-	-	-	-	-	-	-	-	-	-	-	-
9
10	Reporting Page
11	Date Input	05/31/22
12			YTD May Output
13	Dept Input	400100	#REF!
14	Dept Input	400101	#REF!
15	Dept Input	400160	#REF!
16	Dept Input	400170	#REF!
17	Dept Input	400171	#REF!
Sheet1 (2)

Cell Formulas
Range	Formula
C13:C17	C13	=SUM(FILTER(FILTER($B$4:$M$8,(MONTH(B$3:M$3)<=MONTH($B$11))),A:A#=B13))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B11	Expression	=#REF!<>0	text	YES
E1	Expression	=#REF!<>0	text	YES
B10:B11	Expression	=#REF!<>0	text	YES
G1:M1,C1,B10:B11,N1:N2	Expression	=#REF!<>0	text	YES

 

[awoohaw]

I come up with this, without filter:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	RAW Data
3	DEPT	2022-01-31	2022-02-28	2022-03-31	2022-04-30	2022-05-31	2022-06-30	2022-07-31	2022-08-31	2022-09-30	2022-10-31	2022-11-30	2022-12-31	1905-07-14
4	400100	9866446.4	6198636.8	6198636.8	6198636.8	11768273.6	14485169.6	44778560	0	0	0	0	0	99494360
5	400101	-587340.07	-388978.11	-242392.4047	-17025.1807	-33785.4168	-55537.9333	-134921.827	0	0	0	0	0	-1459980.94
6	400160	372033.93	372033.93	372033.93	372033.93	372033.93	372033.93	372033.93	7378533.93	7378533.93	7378533.93	5292577.43	5292577.43	35324994.16
7	400170	0	0	0	0	0	0	0	0	0	0	0	0	0
8	400171	0	0	0	0	0	0	0	0	0	0	0	0	0
9
10	Reporting Page
11	Date Input	2022-05-31
12			YTD May Output
13	Dept Input	400100	#REF!	11,768,273.60
14	Dept Input	400101	#REF!	(33,785.42)
15	Dept Input	400160	#REF!	372,033.93
16	Dept Input	400170	#REF!	-
17	Dept Input	400171	#REF!	-
Sheet3

Cell Formulas
Range	Formula
C13:C17	C13	=SUM(FILTER(FILTER($B$4:$M$8,(MONTH(B$3:M$3)<=MONTH($B$11))),A:A#=B13))
D13:D17	D13	=SUMPRODUCT((--($B13=$A$4:$A$8))*(--(MONTH($B$11)=MONTH($B$3:$N$3)))*($B$4:$N$8))