Sum with various criteria

[Robert Davidson]

My question is in the image below which relates to a complex way to sum columns according to a number of criteria. I hope someone might be able to help

 

[KRice]

If you were to put the year 2035 in the red cell, how should coupe 1 be handled (since trees within that coupe are to be felled in that same year)?

 

[Robert Davidson]

I would assume they would not be felled

 

[KRice]

Thanks for the clarification. There may be more efficient ways to obtain an answer, but I've incorporated everything in a single multi-part formula. This assumes that your two source tables are official Excel tables. If not, you can create them by clicking on a cell in the table range, hit Ctrl-t to open a small dialog box, confirm the range identified covers the full table and indicate that the table has headers. The first table is named Table1, the second, Table2. The formula assigns a number of variables to isolate the columns in Table1 that represent Felling Coupes that would have been felled prior to the year specified in cell O2, and then those columns (Coupes) are stacked vertically...that's "fld". Because there may be multiple coupes represented, I created a flattened array (a two-column array identifying the Species code and the corresponding number of hectares associated with each Block in the identified Coupes...that's "flst". Then the two-column array is transformed into an N column array, where N represents the number of unique species in Table1. Each element within a column is either TRUE or FALSE, indicating whether the quantity in flst in that same position corresponds to the species represented by that column...that's "slst". Then some matrix multiplication is used to find the sum of felled hectares for each of the species...that's "fell".

The same steps are repeated for the Not Felled determination, and each of those variables resembles the ones just described, except these variable names begin with the letter "n".

Finally, the "fell" and "nfell" arrays are added together to create a "total" array, and then the unique species array ("spc") is stacked together with "fell", "nfell", and "total" for the result.

MrExcel_20231219.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2				FELLING COUPES (Hectares)										Sell Year	2036
3
4	Block	Species	Area (ha)	1	2	3	4	Check Area		Coupes	Fell Year	Area		Species	Felled	Not Felled	Total
5	A	SS	50	30	8	2	10	50		1	2035	55		DF	15	0	15
6	B	SS	25	20	0	2	3	25		2	2040	14		LP	0	12	12
7	C	LP	12	0	6	0	6	12		3	2025	14		SS	54	21	75
8	D	DF	15	5	0	10	0	15		4	2050	19
9
10
11	Totals		102					102				102
Sheet6

Cell Formulas
Range	Formula
N5:Q7	N5	=LET(spc,SORT(UNIQUE(Table1[Species])), t1cnt,ROWS(Table1), fld,TOCOL(CHOOSECOLS(Table1,MATCH(FILTER(Table2[Coupes]&"",Table2[Fell Year]<O2),Table1[#Headers],0)),,1), fcnt,ROWS(fld), flst,HSTACK(INDEX(Table1[Species],MOD(SEQUENCE(fcnt)-1,t1cnt)+1),fld), slst,INDEX(TAKE(flst,,1),SEQUENCE(fcnt))=TRANSPOSE(spc), fell,TRANSPOSE(MMULT(TRANSPOSE(DROP(flst,,1)),--slst)), nfld,TOCOL(CHOOSECOLS(Table1,MATCH(FILTER(Table2[Coupes]&"",Table2[Fell Year]>=O2),Table1[#Headers],0)),,1), nfcnt,ROWS(nfld), nflst,HSTACK(INDEX(Table1[Species],MOD(SEQUENCE(nfcnt)-1,t1cnt)+1),nfld), nslst,INDEX(TAKE(nflst,,1),SEQUENCE(nfcnt))=TRANSPOSE(spc), nfell,TRANSPOSE(MMULT(TRANSPOSE(DROP(nflst,,1)),--nslst)), total,fell+nfell, HSTACK(spc,fell,nfell,total))
H5:H8	H5	=SUM(D5:G5)
L5:L8	L5	=SUM(FILTER(Table1,Table1[#Headers]=J5&""))
C11,L11,H11	C11	=SUM(Table1[Area (ha)])
Dynamic array formulas.

 

[Alex Blakenburg]

Leveraging off @KRice data and perhaps not as all encompassing but a bit less complex here is another option.

2023 Filter with Lookup Robert Davidson.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2				FELLING COUPES (Hectares)										Sell Year	2032
3
4	Block	Species	Area (ha)	1	2	3	4	Check Area		Coupes	Fell Year	Area		Species	Felled	Not Felled	Total
5	A	SS	50	30	8	2	10	50		1	2035	55		SS	4	71	75
6	B	SS	25	20	0	2	3	25		2	2040	14		DF	10	5	15
7	C	LP	12	0	6	0	6	12		3	2025	14		LP	0	12	12
8	D	DF	15	5	0	10	0	15		4	2050	19
Data

Cell Formulas
Range	Formula
O5:O7	O5	=SUM(FILTER(FILTER(Table1[[1]:[4]],--(XLOOKUP(Table1[[#Headers],[1]:[4]],TRIM(Table2[Coupes]),Table2[Fell Year],"")<$O$2),Table1[[1]:[4]]),Table1[Species]=$N5,""))
P5:P7	P5	=SUM(FILTER(FILTER(Table1[[1]:[4]],--(XLOOKUP(Table1[[#Headers],[1]:[4]],TRIM(Table2[Coupes]),Table2[Fell Year],"")>=$O$2),Table1[[1]:[4]]),Table1[Species]=$N5,""))
Q5:Q7	Q5	=SUM(O5:P5)
H5:H8	H5	=SUM(D5:G5)
L5:L8	L5	=SUM(FILTER(Table1,Table1[#Headers]=J5&""))

 

[Robert Davidson]

Thanks for the clarification. There may be more efficient ways to obtain an answer, but I've incorporated everything in a single multi-part formula. This assumes that your two source tables are official Excel tables. If not, you can create them by clicking on a cell in the table range, hit Ctrl-t to open a small dialog box, confirm the range identified covers the full table and indicate that the table has headers. The first table is named Table1, the second, Table2. The formula assigns a number of variables to isolate the columns in Table1 that represent Felling Coupes that would have been felled prior to the year specified in cell O2, and then those columns (Coupes) are stacked vertically...that's "fld". Because there may be multiple coupes represented, I created a flattened array (a two-column array identifying the Species code and the corresponding number of hectares associated with each Block in the identified Coupes...that's "flst". Then the two-column array is transformed into an N column array, where N represents the number of unique species in Table1. Each element within a column is either TRUE or FALSE, indicating whether the quantity in flst in that same position corresponds to the species represented by that column...that's "slst". Then some matrix multiplication is used to find the sum of felled hectares for each of the species...that's "fell".

The same steps are repeated for the Not Felled determination, and each of those variables resembles the ones just described, except these variable names begin with the letter "n".

Finally, the "fell" and "nfell" arrays are added together to create a "total" array, and then the unique species array ("spc") is stacked together with "fell", "nfell", and "total" for the result.

MrExcel_20231219.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2				FELLING COUPES (Hectares)										Sell Year	2036
3
4	Block	Species	Area (ha)	1	2	3	4	Check Area		Coupes	Fell Year	Area		Species	Felled	Not Felled	Total
5	A	SS	50	30	8	2	10	50		1	2035	55		DF	15	0	15
6	B	SS	25	20	0	2	3	25		2	2040	14		LP	0	12	12
7	C	LP	12	0	6	0	6	12		3	2025	14		SS	54	21	75
8	D	DF	15	5	0	10	0	15		4	2050	19
9
10
11	Totals		102					102				102
Sheet6

Cell Formulas
Range	Formula
N5:Q7	N5	=LET(spc,SORT(UNIQUE(Table1[Species])), t1cnt,ROWS(Table1), fld,TOCOL(CHOOSECOLS(Table1,MATCH(FILTER(Table2[Coupes]&"",Table2[Fell Year]<O2),Table1[#Headers],0)),,1), fcnt,ROWS(fld), flst,HSTACK(INDEX(Table1[Species],MOD(SEQUENCE(fcnt)-1,t1cnt)+1),fld), slst,INDEX(TAKE(flst,,1),SEQUENCE(fcnt))=TRANSPOSE(spc), fell,TRANSPOSE(MMULT(TRANSPOSE(DROP(flst,,1)),--slst)), nfld,TOCOL(CHOOSECOLS(Table1,MATCH(FILTER(Table2[Coupes]&"",Table2[Fell Year]>=O2),Table1[#Headers],0)),,1), nfcnt,ROWS(nfld), nflst,HSTACK(INDEX(Table1[Species],MOD(SEQUENCE(nfcnt)-1,t1cnt)+1),nfld), nslst,INDEX(TAKE(nflst,,1),SEQUENCE(nfcnt))=TRANSPOSE(spc), nfell,TRANSPOSE(MMULT(TRANSPOSE(DROP(nflst,,1)),--nslst)), total,fell+nfell, HSTACK(spc,fell,nfell,total))
H5:H8	H5	=SUM(D5:G5)
L5:L8	L5	=SUM(FILTER(Table1,Table1[#Headers]=J5&""))
C11,L11,H11	C11	=SUM(Table1[Area (ha)])
Dynamic array formulas.

Thank you very much for your help with this Kirk. It works perfectly.

Robert

 

[Robert Davidson]

Leveraging off @KRice data and perhaps not as all encompassing but a bit less complex here is another option.

2023 Filter with Lookup Robert Davidson.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2				FELLING COUPES (Hectares)										Sell Year	2032
3
4	Block	Species	Area (ha)	1	2	3	4	Check Area		Coupes	Fell Year	Area		Species	Felled	Not Felled	Total
5	A	SS	50	30	8	2	10	50		1	2035	55		SS	4	71	75
6	B	SS	25	20	0	2	3	25		2	2040	14		DF	10	5	15
7	C	LP	12	0	6	0	6	12		3	2025	14		LP	0	12	12
8	D	DF	15	5	0	10	0	15		4	2050	19
Data

Cell Formulas
Range	Formula
O5:O7	O5	=SUM(FILTER(FILTER(Table1[[1]:[4]],--(XLOOKUP(Table1[[#Headers],[1]:[4]],TRIM(Table2[Coupes]),Table2[Fell Year],"")<$O$2),Table1[[1]:[4]]),Table1[Species]=$N5,""))
P5:P7	P5	=SUM(FILTER(FILTER(Table1[[1]:[4]],--(XLOOKUP(Table1[[#Headers],[1]:[4]],TRIM(Table2[Coupes]),Table2[Fell Year],"")>=$O$2),Table1[[1]:[4]]),Table1[Species]=$N5,""))
Q5:Q7	Q5	=SUM(O5:P5)
H5:H8	H5	=SUM(D5:G5)
L5:L8	L5	=SUM(FILTER(Table1,Table1[#Headers]=J5&""))

This too works perfectly so thank you very much as well Alex. I also had a go myself after doing a bit of research and not using Tables I came up with this:
For Cell O5 =SUM(FILTER(CHOOSECOLS($D$5:$G$8,FILTER($J$5:$J$8,$K$5:$K$8<SellYr)),$B$5:$B$8=N5))
For Cell P5 =SUM(FILTER(CHOOSECOLS($D$5:$G$8,FILTER($J$5:$J$8,$K$5:$K$8>=SellYr)),$B$5:$B$8=N5))

It's not as dynamic and doesn't use tables

 

[Alex Blakenburg]

Thanks for the feedback. Glad we could help.