Count number of holidays

[tinybear77]

I have attached my spreadsheet with the info. I would like to know how I count how many holidays (H) each team has taken in the current month. But not sure how to do it??

[daddylonglegs]

What's a "Team" in this context, are they shown in column D?

If so you could use a formula like this to get "Public" Team's holidays in Dec 2010

=SUMPRODUCT((A2:A100=2010)*(B2:B100="Dec")*(D2:D100="Public")*(F2:O100="H"))

You can replace 2010, "Dec", "Public" with cell references so you can easily change, e.g.

=SUMPRODUCT((A2:A100=M2)*(B2:B100=N2)*(D2:D100=O2)*(F2:O100="H"))

where year is in M2, month in N2 and Team in O2

[ChemistB]

Assuming that by "team" you mean department
I put a unique list of departments from Q2 down. In R2
=SUMPRODUCT(--($F$2:$O$100="H")*($D$2:$D$100=$Q2)*($B$2:$B$100=TEXT(TODAY(),"mmm")))
You might want to divide this by 2 since you have two H's per day.
Does that work for you?

[alansidman]

See attached for one solution using =Countif(Range,Criteria)