CODEWITHSUNDEEP
arraysexcelformula
Shahid Thaika
Shahid Thaika

Reputation: 2305

COUNTIF on a manual array list

If I perform simple formulas such as sum() or count(), I can make it work with a manual array list, such as this...

=SUM({1;2;2;2;3})

However, when I do something like the below, it does not work.

=SUMIF({1;2;2;2;3}, 2, {1;2;2;2;3})

Does anyone know how to make something like this work, or know an alternative.

In reality, I use a fixed range, typed in a separate worksheet, in a particular formula, and my boss wants me to avoid having that extra worksheet. Hence, I was thinking of typing out the list in a single formula.

Upvotes: 0

Views: 192

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34180

You could simulate the SUMIF using a SUM with multiplication:-

=SUM({1,2,2,2,3} *({1,2,2,2,3}=2))

or

=SUM({1;2;2;2;3} *({1;2;2;2;3}=2))

This has to be entered as an array formula using CtrlShiftEnter

You can also use SUMPRODUCT if you prefer to avoid a CSE formula:-

=SUMPRODUCT({1,2,2,2,3} *({1,2,2,2,3}=2))

Upvotes: 2

Related Questions