Using Month query inside sumif formula

[Jpull]

Hi, First time posting....

I am trying to create a formula to sum the columns if the month is greater than or equal to todays month.

For example:

Row / Column	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​
11	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sept-24	Oct-24	Nov-24	Dec-24	Sum
13	Data 1	5	5	5	5	5	0.25	-2	-1	-0.5	3	-2	1
14	Data 2	10	10	10	10	10	-3	-5	-2	-2	1	-4	2

The formula in the Sum column should only calculate the column amounts fr the row when the month is greater than or equal to today. So as today is June, the Sum for Data Line 1 should be: -1.75 and for data line 2: -13

I have the month of todays date showing in cell B1 of my spreadsheet to also use in the formula.

I've so far tried with SUMIFS, SUMPRODUCT and a couple of others but at present i'm not getting it to work.

If i use: =SUMIF(E11:P11,$B$1<=MONTH(E11:P11),E13:P13), then the formula spills but doesnt work

If i use: =SUMIF(E11:P11,$B$1<=MONTH(),E13:P13), the formula errors

Any help or suggestions welcome, i'm sure i've just overlooked something simple

 

[johnny51981]

The cleanest way I could come up with does include the addition of a helper row to populate the month number under the month header in row 11. I've placed that in Row 12.

=SUMIF(D$12:O$12,">="&MONTH($B$1),$D13:$O13)

 

[hagia_sofia]

Or maybe:

=SUM(FILTER(E13:P13,EOMONTH(+$E$11:$P$11,0)>=$B$1))

 

[awoohaw]

Here is a solution using SUMPRODUCT function:
but I come up with different results:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
4			Month:	2024-06-01
5
6
7
8
9
10			Row / Column	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
11			11	24-Jan	24-Feb	24-Mar	24-Apr	24-May	24-Jun	24-Jul	24-Aug	24-Sep	24-Oct	24-Nov	24-Dec	Sum
12			13	Data 1	5	5	5	5	5	0.25	-2	-1	-0.5	3	-2	2.75
13			14	Data 2	10	10	10	10	10	-3	-5	-2	-2	1	-4	-5
14
15					0	0	0	0	10	-3	-5	-2	-2	1	-4
Sheet1

Cell Formulas
Range	Formula
P12	P12	=SUMPRODUCT(((MONTH(D4)<=MONTH($E$11:$O$11))*(E12:O12)))
P13	P13	=SUMPRODUCT(((MONTH(D4)<=MONTH($E$11:$O$11))*(E13:O13)))
E15:O15	E15	=(((MONTH(D4)<=MONTH($E$11:$O$11))*(E13:O13)))
Dynamic array formulas.

 

[Snakehips]

I'm assuming that your posted data is inadvertently offset to the right by one column?

However, assuming that D11:O11 is the first of each month then here is my SUMIF suggestion.

AutoTraining.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P
11	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	SUM
12		5	5	5	5	5	0.25	-2	-1	-0.5	3	-2	2.75
13		10	10	10	10	10	-3	-5	-2	-2	1	-4	-5
Sheet1

Cell Formulas
Range	Formula
P12:P13	P12	=SUMIF(D$11:O$11,">="&TODAY()-31,D12:O12)

 

[Jpull]

amazing thank you guys! i used the last option =SUMIF(D$11:O$11,">="&TODAY()-31,D12:O12) which works perfectly, for some reason the other 2 before still didn't give me the result but again i may have overlooked something!

Really appreciate the help and even more so the speed of it - really impressed guys and i will certainly be back next time i have an issue

 

[Jpull]

I'm assuming that your posted data is inadvertently offset to the right by one column?

However, assuming that D11:O11 is the first of each month then here is my SUMIF suggestion.

AutoTraining.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P
11	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	SUM
12		5	5	5	5	5	0.25	-2	-1	-0.5	3	-2	2.75
13		10	10	10	10	10	-3	-5	-2	-2	1	-4	-5
Sheet1

Cell Formulas
Range	Formula
P12:P13	P12	=SUMIF(D$11:O$11,">="&TODAY()-31,D12:O12)

This was great thank you Snakechips!