CODEWITHSUNDEEP
excel-formulaconditional-statementscurly-braces
ikelovesexcel
ikelovesexcel

Reputation: 1

Excel conditional sum with criteria in curly brackets

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.net/9Q5qWh7K.png)

Upvotes: 0

Views: 37

Answers (1)

Excellor
Excellor

Reputation: 875

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

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

Result

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.

Upvotes: 0

Related Questions