CODEWITHSUNDEEP
excelexcel-formula
Matt Fitzmaurice
Matt Fitzmaurice

Reputation: 1416

Non-contiguous Count

I have the following data:

    M    T    W    T    F    S    S    ...
Matt                    x     
Dave     x              x
Jim                x

Where the days of the week are listed along the top for the current year and sick days are indicated via a 'x'.

How can I get a count of Friday Sick Days for Matt ?

Upvotes: 0

Views: 33

Answers (1)

Jon
Jon

Reputation: 224

More a superuser question I think. But in modern (2007+) excel,

Assuming the days of the week are on row 1, and Matt's records are on row 2

=COUNTIFS($B2:$AA2,"x",$B$1:$AA$1,"F")

In old fashioned (<2007) excel

=SUMPRODUCT(--($B2:$AA2="x"),--($B$1:$AA$1="F"))

Note I'm not specifically picking up Matt in these formulae, I'm relying on the fact that Matt is always on a specific row. Also I'm assuming the data only goes up to column AA - extend if necessary.

Upvotes: 3

Related Questions