Looking to sumif data from month 1 to N

[SmileItOff]

Hello Misters/Misses,

I'm trying to setup a table for some Year-to-Date data from a massive list. Would like to have a formula/vba that pulls different data and sum/count them from month 1 to N, in which N serves as an input. For example, user (my boss) input 4 as April, the table then pulls the data from month 1 to 4, and so on.

Please help.

Thank you!
Sm: )e

 

[jtakw]

Hi,

It would be helpful to help you if you provide a sample of the layout you're using.

May be something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Jan-2018	Feb-2018	Mar-2018	Apr-2018	May-2018	Jun-2018	Jul-2018	Aug-2018	Sep-2018	Oct-2018	Nov-2018	Dec-2018		Input	4
2	100	200	300	400	500	600	700	800	900	1000	1100	1200		Total	1000
Sheet79

Cell Formulas
Range	Formula
O2	=SUMPRODUCT((MONTH(A1:L1)<=O1)*(A2:L2))

 

[SmileItOff]

Hi,

It would be helpful to help you if you provide a sample of the layout you're using.

May be something like this?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
Input
Total

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Jan-2018[/TD]
[TD="align: right"]Feb-2018[/TD]
[TD="align: right"]Mar-2018[/TD]
[TD="align: right"]Apr-2018[/TD]
[TD="align: right"]May-2018[/TD]
[TD="align: right"]Jun-2018[/TD]
[TD="align: right"]Jul-2018[/TD]
[TD="align: right"]Aug-2018[/TD]
[TD="align: right"]Sep-2018[/TD]
[TD="align: right"]Oct-2018[/TD]
[TD="align: right"]Nov-2018[/TD]
[TD="align: right"]Dec-2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1000[/TD]

</tbody>

Sheet79​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O2[/TH]
[TD="align: left"]=SUMPRODUCT((MONTH(A1:L1)<=O1)*(A2:L2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hello J,

Thank you! Let me provide you with an example to better explain my question:

[TABLE="width: 505"]
<tbody>[TR]
[TD]Position[/TD]
[TD]Time elapsed[/TD]
[TD]location[/TD]
[TD]Date closed[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD]Position 1[/TD]
[TD]0-30[/TD]
[TD]location 1[/TD]
[TD]4/2/2018[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Position 2[/TD]
[TD]30-61[/TD]
[TD]location 2[/TD]
[TD]5/19/2018[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Position 3[/TD]
[TD]61-90[/TD]
[TD]location 3[/TD]
[TD]1/28/2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Position 4[/TD]
[TD]61-90[/TD]
[TD]location 1[/TD]
[TD]6/5/2018[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Position 5[/TD]
[TD]61-90[/TD]
[TD]location 5[/TD]
[TD]2/15/2018[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

This is how my data look like. I'm looking for a formula or vba code that counts the number of appearances of each criteria from month 1 to N.

Like if I want to know how many positions were filled within 61-90 days from Month 1-3, it would return 2. Whereas if I want to know the same for month 1-6, it would return 3.

Thank you very much!
Sm: )e

 

[jtakw]

Thanks for posting sample data.

If you have the COUNTIFS function:

Book1
	A	B	C	D	E	F	G	H	I
1	Position	Time elapsed	location	Date closed	Month		Criteria	To Month	Result
2	Position 1	0-30	location 1	4/2/2018	4		61-90	3	2
3	Position 2	30-61	location 2	5/19/2018	5		30-61	4	0
4	Position 3	61-90	location 3	1/28/2018	1		61-90	6	3
5	Position 4	61-90	location 1	6/5/2018	6		0-30	5	1
6	Position 5	61-90	location 5	2/15/2018	2
Sheet84

Cell Formulas
Range	Formula
I2	=COUNTIFS(B$2:B$6,G2,E$2:E$6,"<="&H2)

Enter your criterion in G and H, I formula copied down.

 

[SmileItOff]

Thank you very much! It worked! This is way more comprehensive than I thought. I was thinking about something like factorial in mathematics to solve this problem LOL
Mind if I ask, why it needs an & sign in between "<=" and the referencing cell?

Thank you!!

 

[jtakw]

You're welcome.

Have a look here: https://support.office.com/en-us/article/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842