Forum Discussion

Durwood's avatar
Durwood
Copper Contributor
Sep 12, 2025

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 I'd like to add a third column/array after {"D";"E"}. Can anyone tell me if this can be done or should I just use multiple SUMIFS formulas without arrays. I've tried researching this and the closest answer is to use the semi-colon in the second array. However, I'm unable to find information about adding a third array.

=SUM(SUMIFS(DATA[Eligibility], DATA[Location], {"A","B"}, DATA[SchoolType], {"D";"E"}))

Appreciate your thoughts and suggestions.

excel
Formulas and Functions
office 365

1 Reply

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Sep 12, 2025

    Take this:

     

    1. 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"),
  ...
)

     

    1. 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.

    1. Use LET or LAMBDA for Cleaner Logic

    You can wrap the logic in a LET or LAMBDA function to make it reusable and readable.

Resources