如何在单个单元格中配置多条件数组实现Excel SUMIFS求和？

Great question—this is a common pain point when you want to keep your spreadsheet clean without cluttering it with extra columns for each condition. Here are two robust solutions tailored to different Excel versions:

For Excel 365/2021 (Modern Versions) #

Use TEXTSPLIT to convert your comma-separated criteria in a single cell into an array that SUMIFS can process seamlessly.

Step-by-Step: #

1. Store all your color conditions in one cell (e.g., cell C1 with Blue,Yellow—feel free to use a unique delimiter like | or ; if commas might appear in your actual values).
2. Use this formula:

   =SUM(SUMIFS($A$1:$A$10, $B$1:$B$10, TEXTSPLIT(C1, ",")))
   

   • TEXTSPLIT(C1, ",") turns the single string into an array exactly like your hardcoded example ({"Blue","Yellow"}), letting SUMIFS calculate sums for each condition individually.
   • The outer SUM adds up those separate sums to get your total.

Bonus: Dynamic Delimiter #

If you want to switch delimiters without editing the formula, reference a cell holding your delimiter (e.g., D1 contains ;):

=SUM(SUMIFS($A$1:$A$10, $B$1:$B$10, TEXTSPLIT(C1, D1)))

For Legacy Excel (Pre-365/2021) #

Since TEXTSPLIT isn’t available here, use FILTERXML to parse the comma-separated string into an array:

=SUM(SUMIFS($A$1:$A$10, $B$1:$B$10, FILTERXML("<t><s>"&SUBSTITUTE(C1, ",", "</s><s>")&"</s></t>", "//s")))

• SUBSTITUTE(C1, ",", "</s><s>") wraps each condition in XML tags (e.g., Blue,Yellow becomes Blue</s><s>Yellow).
• FILTERXML extracts each tagged value into an array, which SUMIFS uses just like your original hardcoded array.

Key Tips #

• Pick a delimiter that won’t appear in your actual condition values (e.g., avoid commas if your colors have names like Light Blue, Sky).
• For case-sensitive matching (to distinguish blue vs Blue), swap SUMIFS for SUMPRODUCT with EXACT:

  =SUMPRODUCT($A$1:$A$10*(EXACT($B$1:$B$10, TEXTSPLIT(C1, ","))))
  

  (For legacy Excel, combine this with the FILTERXML method instead of TEXTSPLIT.)

This setup lets you add or remove conditions directly in the single criteria cell without touching the formula—perfect for dynamic, uncluttered spreadsheets!

内容的提问来源于stack exchange，提问作者DAnderson