How to sum data between two dates by project type

I want to sum data between two dates and filter it by Project Type. I tried the SUMIFS functions but it gives me a VALUE error. Please Help. I have attached the file below with all the data and calculation area.

File appears to be password protected. Cannot open file. Please remove and upload an unprotected file that is not linked to any external drive.

in N45

=SUMPRODUCT(($B$22:$B$37=N$44)*($F$2:$BB$2>=$L45)*(($F$2:$BB$2<=$M45)*($F$22:$BB$37)))

copy across and down

	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
44	From	To	Project 1	Project 2	Project 3	Project 4	Project 5	Project 6	Project 8	Project 10	Project 11	Project 13	Project 14	Project 15	Project 16	Project 17	Project 18	Project 19
45	31-Jan-23	31-Dec-23	76,321,875	51,675,000	46,167,188	43,972,396	24,394,531	28,039,500	34,860,938	46,790,625	13,925,781	0	0	0	0	0	0	0
46	31-Jan-24	31-Dec-24	0	4,306,250	39,571,875	37,690,625	34,152,344	56,079,000	55,777,500	105,278,906	41,777,344	42,407,813	35,092,969	35,092,969	0	0	0	0
47	31-Jan-25	31-Dec-25	0	0	0	0	0	0	0	0	0	59,370,938	105,278,906	105,278,906	70,185,938	70,185,938	46,790,625	46,790,625
48	31-Jan-26	31-Dec-26	0	0	0	0	0	0	0	0	0	0	0	0	81,883,594	81,883,594	105,278,906	105,278,906

	L	M	N
44	From	To	Project 1
45	31-Jan-23	31-Dec-23	=SUM(OFFSET($A$1,MATCH(N$44,$B:$B,0)-1,MATCH($L45,$2:$2,0)-1,1,MATCH($M45,$2:$2,0)-MATCH($L45,$2:$2,0)+1))

Drag formula right and down as long as needed.

I had some problems with downloading too, but eventualy I managed.

With such input data layout SUMIFS would not be the right tool. Use SUMPRODUCTS instead. In N45:

Formula:

Please Login or Register  to view this content.

and copy down and right


edit: Wow, 3 propositions in the same minute :-)

At least 3 of us already managed to download previous attachment. See the answers above

For information: the minute a workbook I download starts looking for links and trying to get me to log in to other accounts, I close it down. Anyone sharing a workbook here should ensure that the workbook is completely standalone before posting it.

Here!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

There are numerous Excel courses/training (some FREE) on the Internet so I would start there.

I suppose that question is to me as I did not use SUMPRODUCT.
I've get array of values from cells which met conditions (project number & time brackets) and then put this array inside SUM.
Ex. for N45: =SUM({6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25,6360156.25})

Originally Posted by JohnTopley

There are numerous Excel courses/training (some FREE) on the Internet so I would start there.

And I would add: visiting our Forum and analyse questions & answers

@KOKOSEK: quite right too!!!