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
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

<tbody>
</tbody>

Sheet79​

Worksheet Formulas Cell Formula O2 =SUMPRODUCT((MONTH(A1:L1)<=O1)*(A2:L2)) <tbody> </tbody>

<tbody>
</tbody>

Hello J,

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

Position	Time elapsed	location	Date closed	Month
Position 1	0-30	location 1	4/2/2018	4
Position 2	30-61	location 2	5/19/2018	5
Position 3	61-90	location 3	1/28/2018	1
Position 4	61-90	location 1	6/5/2018	6
Position 5	61-90	location 5	2/15/2018	2

<tbody>
</tbody>

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