Excel conditional sum with criteria in curly brackets - Stack Overflow

admin管理员组

文章数量:1277909

I have a curious issue with excel sum(array = criteria). I'm trying to write a conditional sum for an array with multiple criteria, and to avoid a very long formula, I would want to enter the criteria as a list in curly brackets. I cannot get excel to calculate the result if I enter the criteria, but it works if I double enter and then divide by 2. Any idea why this happens / any around it? I assume there must be something wrong with my syntax, but can't figure out what.

The fomula I wrote that didn't work is: =SUM((A2:A8={"A","B","C"})) A very similar one that did the trick: =SUM((A2:A8={"A","B","C"})+(A2:A8={"A","B","C"}))/2

Screencap has the actual excel version (first formula in B2, result in C2, and second one in B4 / C4.

(.png)

I have a curious issue with excel sum(array = criteria). I'm trying to write a conditional sum for an array with multiple criteria, and to avoid a very long formula, I would want to enter the criteria as a list in curly brackets. I cannot get excel to calculate the result if I enter the criteria, but it works if I double enter and then divide by 2. Any idea why this happens / any around it? I assume there must be something wrong with my syntax, but can't figure out what.

The fomula I wrote that didn't work is: =SUM((A2:A8={"A","B","C"})) A very similar one that did the trick: =SUM((A2:A8={"A","B","C"})+(A2:A8={"A","B","C"}))/2

Screencap has the actual excel version (first formula in B2, result in C2, and second one in B4 / C4.

(https://i.sstatic/9Q5qWh7K.png)

• excel-formula
• conditional-statements
• curly-braces

Share Improve this question Follow edited Feb 25 at 9:27 ikelovesexcel asked Feb 25 at 9:23 ikelovesexcelikelovesexcel 111 bronze badge 2

• I don't know what Excel version / edition you have, but your formulas cause my Excel (MS 365 Apps for business, v2501) to pop up saying "There's a problem with this formula" followed by basic formula tips... Not your fault, but others may also encounter this. – Peter B Commented Feb 25 at 9:40
• Interesting. I have MS Excel for Microsoft 365 MSO v2412. So desktop, older version based on the numbering at least. – ikelovesexcel Commented Feb 25 at 9:51

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Based on your screencap, it looks like you're trying to use SUM(COUNTIFS()):

=SUM(COUNTIF(A2:A8,{"A","B","C"}))

Row 1 is using your formula (where you made it work multiplying it by 1);

Row 2 (selected cell) is using SUM(COUNTIFS());

Row 3 is just using COUNTIFS.

本文标签： Excel conditional sum with criteria in curly bracketsStack Overflow