How to count a value across multiple columns in Excel

Q: How do I count across columns on different sheets?

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

Q: 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.

Formula (Excel and Google Sheets)

=COUNTIF(B2:F6,H1)

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

Result on the example: 8 in H2
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in H2. Change any input and the result updates, or edit the formula to experiment.

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.

=COUNTIF(B2:F6,H1)

Result8

B2:F65 rows × 5 cols: "Ana", "Ben", "Cara", "Ana", …
A range: 5 rows × 5 columns.
H1"Ana"
The value in H1.
COUNTIF(B2:F6,H1)8
COUNTIF: Counts cells that meet one condition.
- rangeB2:F65 rows × 5 cols: "Ana", "Ben", "Cara", "Ana", …
- criteriaH1"Ana"

In Google Sheets

The same formula works in Google Sheets: =COUNTIF(B2:F6,H1). We ran it in Google Sheets mode on the same data and it returns 8.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=SUMPRODUCT(--(B2:F6=H1))	8	A direct comparison on the block; the same count.
=COUNTIF(B2:B6,H1)+COUNTIF(F2:F6,H1)	4Different result	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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count a value across multiple columns

In H2, count how many on-call shifts the person in H1 has across the whole rota B2:F6.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.