Sum with various criteria

R

Robert Davidson

New Member
Joined
Aug 8, 2023
Messages
29
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

1703268622314.png
 

Attachments

  • 1703268435191.png
    1703268435191.png
    50.6 KB · Views: 8

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
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)?
 
Upvote 0
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
ABCDEFGHIJKLMNOPQ
1
2FELLING COUPES (Hectares)Sell Year2036
3
4BlockSpeciesArea (ha)1234Check AreaCoupesFell YearAreaSpeciesFelledNot FelledTotal
5ASS50308210501203555DF15015
6BSS2520023252204014LP01212
7CLP120606123202514SS542175
8DDF1550100154205019
9
10
11Totals102102102
Sheet6
Cell Formulas
RangeFormula
N5:Q7N5=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:H8H5=SUM(D5:G5)
L5:L8L5=SUM(FILTER(Table1,Table1[#Headers]=J5&""))
C11,L11,H11C11=SUM(Table1[Area (ha)])
Dynamic array formulas.
 
Upvote 1
Solution
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
ABCDEFGHIJKLMNOPQ
1
2FELLING COUPES (Hectares)Sell Year2032
3
4BlockSpeciesArea (ha)1234Check AreaCoupesFell YearAreaSpeciesFelledNot FelledTotal
5ASS50308210501203555SS47175
6BSS2520023252204014DF10515
7CLP120606123202514LP01212
8DDF1550100154205019
Data
Cell Formulas
RangeFormula
O5:O7O5=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:P7P5=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:Q7Q5=SUM(O5:P5)
H5:H8H5=SUM(D5:G5)
L5:L8L5=SUM(FILTER(Table1,Table1[#Headers]=J5&""))
 
Upvote 1
KRice said:
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
ABCDEFGHIJKLMNOPQ
1
2FELLING COUPES (Hectares)Sell Year2036
3
4BlockSpeciesArea (ha)1234Check AreaCoupesFell YearAreaSpeciesFelledNot FelledTotal
5ASS50308210501203555DF15015
6BSS2520023252204014LP01212
7CLP120606123202514SS542175
8DDF1550100154205019
9
10
11Totals102102102
Sheet6
Cell Formulas
RangeFormula
N5:Q7N5=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:H8H5=SUM(D5:G5)
L5:L8L5=SUM(FILTER(Table1,Table1[#Headers]=J5&""))
C11,L11,H11C11=SUM(Table1[Area (ha)])
Dynamic array formulas.
Click to expand...
Thank you very much for your help with this Kirk. It works perfectly.

Robert
 
Upvote 0
Alex Blakenburg said:
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
ABCDEFGHIJKLMNOPQ
1
2FELLING COUPES (Hectares)Sell Year2032
3
4BlockSpeciesArea (ha)1234Check AreaCoupesFell YearAreaSpeciesFelledNot FelledTotal
5ASS50308210501203555SS47175
6BSS2520023252204014DF10515
7CLP120606123202514LP01212
8DDF1550100154205019
Data
Cell Formulas
RangeFormula
O5:O7O5=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:P7P5=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:Q7Q5=SUM(O5:P5)
H5:H8H5=SUM(D5:G5)
L5:L8L5=SUM(FILTER(Table1,Table1[#Headers]=J5&""))
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Sumifs or Sumproduct formula within a month with multiple criteria
Replies
6
Views
744
rrmando18
R
Enigmachrysalis
  • Question Question
SUMIFS with duplicate values
Replies
7
Views
380
Fluff
Fluff
B
  • Solved
Sum(Sumifs)
Replies
3
Views
261
RoryA
RoryA
G
  • Question Question
SUMIFS multiple criteria, the CRITERIA (not the CRITERIA RANGE) is a range and it has blank cells
Replies
6
Views
1K
Fluff
Fluff
M
  • Question Question
Sumifs with SUM(IF(FREQUENCY(MATCH(... formula
Replies
8
Views
380
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,878
Messages
6,175,141
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top