Your question, your audience. Choose who sees your identity—and your question—with question security.

What is the proper syntax in an Excel formula to count multiple values.

Ex. I want to know the amount records that appear with both 02 and 03 in the same column. I currently have the formula only counting 02.

Screenshot attached.

Capture.PNG

Ex. I want to know the amount records that appear with both 02 and 03 in the same column. I currently have the formula only counting 02.

Screenshot attached.

Capture.PNG

```
=COUNTIF(A12:A400,"02")+COUNTIF(A12:A400,"03")
```

```
=SUMPRODUCT(--(A12:A400={"02","03"}))
```

Use [Ctrl]+[Shift]+[Enter] to enter the formula.

-Glenn

If you use Sumproduct as suggested by me, you don't need to place an Array formula that is where Sumproduct has an edge over Sum function as Sumproduct can handle arrays.

Why do I keep getting the error attached?

Capture.PNG

Capture.PNG

Try this to see if that works for you.....

```
=SUM(COUNTIF(A12:A400;{"02";"03"}))
```

If the formula is copied from the code snippet below and pasted in maximus1974's workbook, it should work without error.

```
=SUM(COUNTIF(A12:A400,{"02","03"}))
```

Capture.PNG

Capture2.PNG

```
=SUM(COUNTIF(A12:A400|{"02","03"}))
```

OR```
=SUM(COUNTIF(A12:A400|{"02"|"03"}))
```

If you use a US version of Excel, I strongly suggest that you change your list separator character to a comma. I've never seen anyone use a pipe symbol like shown in Capture2.PNG in the Region and Language...Customize Format...Numbers control panel--and if you post questions on an Excel forum, everybody assumes that you use a comma as the list separator.

Brad

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

=SUM(COUNTIF(A12:A400,{"02

Since COUNTIF counts both text and numbers (converting the text into numbers if that is possible), you can simplify even further:

=SUM(COUNTIF(A12:A400,{2,3