CODEWITHSUNDEEP
excelexcel-formulacountif
Korv
Korv

Reputation: 259

CountIf column header matches day of week

I have a spreadsheet with a table containing multiple weeks, it has columns and rows like so:

             Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon
  1. User 1
  2. User 2
  3. User 3
  4. User 4

A letter "H" will be enters for a users/day when they have booked some holiday, I need to count for each user the number of each days that they have taken as holiday.

I wrote the formula: =COUNTIF(INDEX($N:$NMMATCH("Sun",$N:$NM)),"H")

But this obviously it only returns a count for the first "Sun" column it finds and ignores all the Sundays after.

Does anyone know how this can be accomplished?

Upvotes: 2

Views: 1989

Answers (2)

K_B
K_B

Reputation: 3678

Use COUNTIFS:

=COUNTIFS(2:2;"H";$1:$1;"Sun")

Now if you make a small table with Mon up to Sun as headers above it and refer to that for the 4th parameter you can ditch the "Sun"

Upvotes: 2

barry houdini
barry houdini

Reputation: 46331

If the range for user 1 is N2:NM2 and the headers are in N1:NM1 try this formula to count Suns with "H"

=COUNTIFS(N$1:NM$1,"Sun",N2:NM2,"H")

You can copy that down for all users

Upvotes: 2

Related Questions