formulas with more than one condition, one of which is a timeframe

[Tim]

Please can someone help??, here is my problem:

I am creating a work request recording log, the info entered in column C
(cells C3 to C202)describes the dept that submits the request (e.g. "R2FF" or
"RPGK"). The info entered into column D (cells D3 to D202) states the time
which the request was received (I have time formatted this column, e.g
1:11AM). the info entered into colum G (cells G3 to G202) is numeric stating
how many visits required.

i need to create the formulas that:
1) if anything in cells C3-C202 = "R2FF or R2FL" and the time in
corresponding cell in column D is between 7:00AM and 8:AM then the occurence
is added in cell AC3

2) As above but to add up the values in corresponding cell in column G and
show total in cell AC7

if anyone can help me i will be able to ween myself off the advil

thanks,

 

[Ron Coderre]

Try these (in sections for readability):

AC3:
=SUMPRODUCT((C3:C202={"R2FF","RPGK"})*
((D3202>=TIME(7,,))*(D3202<=TIME(8,,))))

AC7:
=SUMPRODUCT((C3:C202={"R2FF","RPGK"})*((D3202>=TIME(7,,))*
(D3202<=TIME(8,,)))*E3:E202)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Tim]

Ron,

thank you!! it works a treat,
--
Tim

Try these (in sections for readability):

AC3:
=SUMPRODUCT((C3:C202={"R2FF","RPGK"})*
((D3202>=TIME(7,,))*(D3202<=TIME(8,,))))

AC7:
=SUMPRODUCT((C3:C202={"R2FF","RPGK"})*((D3202>=TIME(7,,))*
(D3202<=TIME(8,,)))*E3:E202)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)