How to search a specific column based on another cell?

[ivanbin]

So here I have a table which shows which months have 4 of a specific week day and which have 5.
I am doing this because for my work I work with people who get X amount of hours of service per month. So for example if someone gets 12 hours of service per month and gets it for 3 hours every Saturday, they will go over their 12 hours if the month has 5 Saturdays. So I need to know how often that happens (and preferably on which months if thats possible).

 

[ivanbin]

Cant seem to find edit button. Here's the image I was to attach:

https://i.imgur.com/fh6il9q.png

 

[Marcelo Branco]

Welcome to mr Excel forum

Maybe something like this

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Month​

[/TD]
[TD]

Monday​

[/TD]
[TD]

Tuesday​

[/TD]
[TD]

Wednesday​

[/TD]
[TD]

Thursday​

[/TD]
[TD]

Friday​

[/TD]
[TD]

Saturday​

[/TD]
[TD]

Sunday​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

January​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

February​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

March​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

April​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

May​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

June​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

July​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

August​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

September​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

October​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

November​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

December​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

Count > 4​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD][/TD]
[TD]

List​

[/TD]
[TD]

List​

[/TD]
[TD]

List​

[/TD]
[TD]

List​

[/TD]
[TD]

List​

[/TD]
[TD]

List​

[/TD]
[TD]

List​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

April​

[/TD]
[TD="bgcolor: #D9D9D9"]

January​

[/TD]
[TD="bgcolor: #D9D9D9"]

January​

[/TD]
[TD="bgcolor: #D9D9D9"]

January​

[/TD]
[TD="bgcolor: #D9D9D9"]

March​

[/TD]
[TD="bgcolor: #D9D9D9"]

March​

[/TD]
[TD="bgcolor: #D9D9D9"]

March​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

July​

[/TD]
[TD="bgcolor: #D9D9D9"]

April​

[/TD]
[TD="bgcolor: #D9D9D9"]

May​

[/TD]
[TD="bgcolor: #D9D9D9"]

May​

[/TD]
[TD="bgcolor: #D9D9D9"]

May​

[/TD]
[TD="bgcolor: #D9D9D9"]

June​

[/TD]
[TD="bgcolor: #D9D9D9"]

June​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

September​

[/TD]
[TD="bgcolor: #D9D9D9"]

July​

[/TD]
[TD="bgcolor: #D9D9D9"]

July​

[/TD]
[TD="bgcolor: #D9D9D9"]

August​

[/TD]
[TD="bgcolor: #D9D9D9"]

August​

[/TD]
[TD="bgcolor: #D9D9D9"]

August​

[/TD]
[TD="bgcolor: #D9D9D9"]

September​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

December​

[/TD]
[TD="bgcolor: #D9D9D9"]

October​

[/TD]
[TD="bgcolor: #D9D9D9"]

October​

[/TD]
[TD="bgcolor: #D9D9D9"]

October​

[/TD]
[TD="bgcolor: #D9D9D9"]

November​

[/TD]
[TD="bgcolor: #D9D9D9"]

November​

[/TD]
[TD="bgcolor: #D9D9D9"]

December​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]

December​

[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
</tbody>[/TABLE]


Formula in B15 copied across
=COUNTIF(B2:B13,">4")

Array formula in B17 copied across and down (gray area)
=IF(ROWS(B$17:B17)>B$15,"",INDEX($A$2:$A$13,SMALL(IF(B$2:B$13>4,ROW($A$2:$A$13)-ROW($A$2)+1),ROWS(B$17:B17))))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.