COUNTIF with Two Criteria

[polocar]

Hi,
I use Excel 2003 and I have the following problem:
I have 3 columns,
A containing a list of employees (MICHAEL, BOB, MIKE, etc.)
B containing their work starting hour (8.00, 8.30, 9.00, etc.)
C containing the possible employee absence reason (ILLNESS, HOLIDAY, INJURY, etc.)

I would like to write a formula that counts the number of employees who have a work starting hour within 7.00 and are not absent.

A possible table is this one:

NAME START ABSENCE
MICHAEL 6.30
BOB 8.30
MIKE 9.00 HOLIDAY
BRIAN 7.00
TOM 6.30 ILLNESS

The formula I'm looking for should calculate "2" (because MICHAEL and BRIAN are the only 2 employees starting work hour within 7.00 and not absent).

As I have thought it could be useful, in another worksheet I have inserted:
in A column the list of all the starting work hours:
0.00 (A2), 0.30 (A3), 1.00 (A4), 1.30 (A5), ... 7.00 (A16), 7.30 (A17), ... 23.30 (A49)
in B column the list of all the absence reasons:
ILLNESS (B2), HOLIDAY (B3), INJURY (B4).
I have defined 2 names, the first called EARLY_MORNING (that I have associated to the range from 0.00 to 7.00 of work starting hours column, that is A2:A16), the second called ABSENCE_REASON (that I have associated to the range (B2:B4) of absence reasons' column).

What kind of formula can I write to obtain what I want (using the 2 names EARLY_MORNING and ABSENCE_REASONS defined in the other worksheet)?

Thank you very much

[teylyn]

Hi,

I'm not sure the range names will help much here. If you specify the time you're looking for in a cell like E1, then one possible formula you could use is

=SUMPRODUCT(--(B2:B10=E1),--ISBLANK(C2:C10))

So, all entries in column B that have the same time value as E1, and all values in column C that do not have any text (which means, they're not absent)

would that work?

[DonkeyOte]

For Early Morning you could use

=SUMPRODUCT(--(HOUR(B2:B10)<=7),--(C2:C10<>""))

Without knowing exact content of C I'd be wary of using ISBLANK as Nulls would be excluded.

However, depending on the volume of calcs you're looking to perform, the volume of source data - I would say you'd be better off IMO investigating use of Pivot Tables.

If for ex. you added a formula adjacent to your transactions that stipulated shift based on start hour, eg:

D1: SHIFT (header)

D2: =IF(A2="","",VLOOKUP(B2,'listsheet'!A:B,2))
copied down for all rows

where listsheet is your sheet on which you listed all start times - adjacent to those times add the name of the shift (ie B2:B16 would be EARLY_MORNING)


You can then push your data into a Pivot Table which will give you lots (and lots) of reporting functionality for little/no effort... regards setup of PT:

set your new D column as ROW Field,
set Column C (absence type) as COLUMN field
use Column C a second time but this time as DATA Field (will default to Count)

thereby giving your a nice matrix output... attached is proof of concept
(you can manually sort your Shifts and absence types by dragging them and then disabling Auto Sort feature)

For a general intro on use of PTs see the link in my sig.

[polocar]

OK guys, thank you very much.
Both your solutions give me the result I was looking for:

=SUMPRODUCT(--(B2:B10=E1),--ISBLANK(C2:C10))

and

=SUMPRODUCT(--(HOUR(B2:B10)<=7),--(C2:C10=""))

DonkeyOte, I have downloaded the file you have attached, and I'm beginning to learn how the pivot table functions (until now I had only heard about that). So, it will be a good opportunity to learn about it.
Thanks a lot again