# How to count a value across multiple columns in Excel

Give COUNTIF the whole block of columns as its range: `=COUNTIF(B2:F6,H1)` counts every cell in all five columns that equals H1.

**Excel:** `=COUNTIF(B2:F6,H1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Week | Mon | Tue | Wed | Thu | Fri | Name | Ana |
| W1 | Ana | Ben | Cara | Ana | Dev | Shifts |  |
| W2 | Ben | Ana | Dev | Cara | Ben |  |  |
| W3 | Cara | Dev | Ana | Ben | Ana |  |  |
| W4 | Dev | Ben | Cara | Ana | Cara |  |  |
| W5 | Ana | Cara | Ben | Dev | Ana |  |  |

Formula in H2: `=COUNTIF(B2:F6,H1)`

Result: 8

## How it works

COUNTIF's range can be any rectangle, not just one column. `B2:F6` covers five weeks of five days, and COUNTIF counts every one of those 25 cells that equals "Ana".

Ana works twice in three of the weeks and once in the other two: 8 shifts.

## Other ways

- `=SUMPRODUCT(--(B2:F6=H1))`: A direct comparison on the block; the same count.
- `=COUNTIF(B2:B6,H1)+COUNTIF(F2:F6,H1)`: For columns that aren't next to each other, add one COUNTIF per column. Here it counts Mondays and Fridays only. (Counts only Ana's Monday and Friday shifts.)

## Common mistakes

- COUNTIFS can't take a block as one condition and a single column as another; all its ranges must be the same shape.
- This counts cells, not rows: a week where Ana works twice counts twice. To count weeks she works at all, see the page on counting rows where any column meets a condition.

## Questions

**How do I count across columns on different sheets?**

Add one COUNTIF per sheet: `=COUNTIF(Jan!B2:F6,H1)+COUNTIF(Feb!B2:F6,H1)`.

**How do I count each person's shifts at once?**

List the names in a column and fill `=COUNTIF($B$2:$F$6,H2)` down next to them.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-a-value-across-multiple-columns