Forum Discussion
Solved
Maximum number of arrays to use with SUMIFS
Hi, I have a situation where I need to sum a column where it would be ideal if SUMIFS could use three (or more) arrays in M365. The following sample SUMIFS nested in a sum statement works fine, but ...
Take this:
- Use Multiple SUMIFS and Add Them
=SUM( SUMIFS(DATA[Eligibility], DATA[Location], "A", DATA[SchoolType], "D", DATA[Program], "X"), SUMIFS(DATA[Eligibility], DATA[Location], "A", DATA[SchoolType], "D", DATA[Program], "Y"), ... )- Use FILTER + SUM for More Flexibility
=SUM(FILTER(DATA[Eligibility], (ISNUMBER(MATCH(DATA[Location], {"A","B"}, 0))) * (ISNUMBER(MATCH(DATA[SchoolType], {"D","E"}, 0))) * (ISNUMBER(MATCH(DATA[Program], {"X","Y"}, 0))) ))This filters the rows where all three conditions are met and then sums the Eligibility column.
- Use LET or LAMBDA for Cleaner Logic
You can wrap the logic in a LET or LAMBDA function to make it reusable and readable.
Take this:
- Use Multiple SUMIFS and Add Them
=SUM(
SUMIFS(DATA[Eligibility], DATA[Location], "A", DATA[SchoolType], "D", DATA[Program], "X"),
SUMIFS(DATA[Eligibility], DATA[Location], "A", DATA[SchoolType], "D", DATA[Program], "Y"),
...
)
- Use FILTER + SUM for More Flexibility
=SUM(FILTER(DATA[Eligibility],
(ISNUMBER(MATCH(DATA[Location], {"A","B"}, 0))) *
(ISNUMBER(MATCH(DATA[SchoolType], {"D","E"}, 0))) *
(ISNUMBER(MATCH(DATA[Program], {"X","Y"}, 0)))
))This filters the rows where all three conditions are met and then sums the Eligibility column.
- Use LET or LAMBDA for Cleaner Logic
You can wrap the logic in a LET or LAMBDA function to make it reusable and readable.