Summing several columns based on criteria

[dracer]

If I have a dataset of 100 rows and 13 columns (1st column being the non numerical data-columns 2-13 would be numerical by month), I was wondering how I could get a formula to add several rows and columns at once based on the number of months. For example in January I would add Column 2 only and in Feb add columns 2&3 and add a column each month (based on the number of months-which would be part of the formula). I know this can be done if the data is all in one row however I was wondering if this can be done if the data is in many rows. For example if I had 30 rows of "ADL" as the criteria in March I would want to add all 30 rows in columns 2 3 and 4 (Jan-March). Does anyone have any ideas?

 

[dreid1011]

What version of Excel are you using? You should update your profile. Also, not sure how your data is entirely setup as far as rows with "ADL" for March, but does this look like what you're after?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	ADL	6	2	4	3	3	3	3	9	7	2	9	8
3	ADL	1	6	9	6	8	9	5	3	7	7	3	2
4	ADL	6	10	7	3	3	5	4	4	9	8	4	10
5	ABX	5	5	6	3	5	10	7	1	6	8	1	3
6	ABX	3	3	3	1	2	10	4	4	10	7	5	2
7	MNO	2	6	9	3	4	1	10	5	8	2	4	2
8	MNO	1	9	3	6	5	9	5	2	5	10	1	5
9	TUV	9	5	5	8	7	9	9	9	8	5	5	4
10	XYZ	9	1	2	4	7	3	3	4	3	6	7	3
11
12	ADL	13	31	51
Sheet2

Cell Formulas
Range	Formula
B12:D12	B12	=SUMPRODUCT(($A$2:$A$10=$A$12)*$B$2:B10)

 

[dracer]

Thanks for responding. I'm using excel 365. That is how the data is laid out. The formula above is similar to a SUMIF. Based on your formula I would have it $B$2:$I$10 for August and $B$2:$J$10 for September. The question is how can I get it to automatically adjust to J10 and K10 automatically based on the date that I have in separate cell on a different worksheet i.e. the value in the cell is is 8/31/24 so it goes to J10 - 9/30/24 it would move to K10 with $B$2 being constant. I know this can be done with and Index:Index type of formula when all the data is in one row, but not when it's in many rows.

 

[dreid1011]

Thanks for responding. I'm using excel 365. That is how the data is laid out. The formula above is similar to a SUMIF. Based on your formula I would have it $B$2:$I$10 for August and $B$2:$J$10 for September. The question is how can I get it to automatically adjust to J10 and K10 automatically based on the date that I have in separate cell on a different worksheet i.e. the value in the cell is is 8/31/24 so it goes to J10 - 9/30/24 it would move to K10 with $B$2 being constant. I know this can be done with and Index:Index type of formula when all the data is in one row, but not when it's in many rows.

Why does the date 8/31/24 mean looking through September(9/30/24)? And what are the exact values in row 1?

 

[dracer]

Why does the date 8/31/24 mean looking through September(9/30/24)? And what are the exact values in row 1?

Sorry. My error. I meant to say 8/31/24 would go up to column I and 9/30/24 would go up to column J. The exact values in row 1 are 1/1/23, 2/1/23 ect formatted as Jan-23 and Feb-23 ect.

 

[dreid1011]

Okay, something like this then, where O1 is the date on sheet 2:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23		8/31/2023
2	ADL	6	2	4	3	3	3	3	9	7	2	9	8
3	ADL	1	6	9	6	8	9	5	3	7	7	3	2
4	ADL	6	10	7	3	3	5	4	4	9	8	4	10
5	ABX	5	5	6	3	5	10	7	1	6	8	1	3
6	ABX	3	3	3	1	2	10	4	4	10	7	5	2
7	MNO	2	6	9	3	4	1	10	5	8	2	4	2
8	MNO	1	9	3	6	5	9	5	2	5	10	1	5
9	TUV	9	5	5	8	7	9	9	9	8	5	5	4
10	XYZ	9	1	2	4	7	3	3	4	3	6	7	3
11
12	ADL	122
Sheet3

Cell Formulas
Range	Formula
B12	B12	=SUM(FILTER(FILTER(B2:M10,(B1:M1<=O1)),A2:A10=A12))

 

[dracer]

Okay, something like this then, where O1 is the date on sheet 2:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23		8/31/2023
2	ADL	6	2	4	3	3	3	3	9	7	2	9	8
3	ADL	1	6	9	6	8	9	5	3	7	7	3	2
4	ADL	6	10	7	3	3	5	4	4	9	8	4	10
5	ABX	5	5	6	3	5	10	7	1	6	8	1	3
6	ABX	3	3	3	1	2	10	4	4	10	7	5	2
7	MNO	2	6	9	3	4	1	10	5	8	2	4	2
8	MNO	1	9	3	6	5	9	5	2	5	10	1	5
9	TUV	9	5	5	8	7	9	9	9	8	5	5	4
10	XYZ	9	1	2	4	7	3	3	4	3	6	7	3
11
12	ADL	122
Sheet3

Cell Formulas
Range	Formula
B12	B12	=SUM(FILTER(FILTER(B2:M10,(B1:M1<=O1)),A2:A10=A12))

Thanks a Million!! This works. I understand what you did and will be able to use this concept for other analysis. I'm not really up to speed on the filter function, but will spend more time studying it.

 

[dreid1011]

Thanks a Million!! This works. I understand what you did and will be able to use this concept for other analysis. I'm not really up to speed on the filter function, but will spend more time studying it.

You're welcome, and happy to help. I used a double FILTER because you can't do row and column in a single FILTER.