Need a Formula for calculating after hours calls-RESOLVED

[snedman]

TsTom has provided EXCELLENT assistance in making it easier to find all the data in the spreadsheet. I am looking for some assistance still in creating a formula that would calculate all calls that came in after 5:30 PM but before 8:30 AM (non-day specific)

The date column is B:9 down and the time column is C:9 down. I would need it to disregard blanks.
Any assistance would be greatly appreciated for this last one.

The following the current formulas in place:

Sunday Calls: =SUMPRODUCT((WEEKDAY(B5:B1066)=1)+0)

Reservation Total: =COUNTIF(E9:E1066,"R")

Message Total: =COUNTIF(E9:E1066,"M")

Exceptions Total: =COUNTIF(E9:E202,"X")+COUNTIF(E9:E202,"I")+COUNTIF(E9:E202,"C")+COUNTIF(E9:E202,"E")+COUNTIF(E9:E202,"V")

Total Number of Calls: =COUNTIF(E9:E1066,"X")+COUNTIF(E9:E1066,"I")+COUNTIF(E9:E1066,"C")+COUNTIF(E9:E1066,"E")+COUNTIF(E9:E1066,"V")+COUNTIF(E9:E1066,"R")+COUNTIF(E9:E1066,"M")



[ This Message was edited by: snedman on 2002-05-06 11:21 ]

[IML]

=COUNTIF(C9:C65536,"<"&"8:30"+0)+COUNTIF(C9:C65536,">"&"17:30"+0)

[Tom Schreiner]

Make sure you delete this line of code in the macro I sent you.
The formula will be overwritten if you do not.

DELETE:

Range("B6") = DayCnt(1) + DayCnt(2) + DayCnt(3) + DayCnt(4) + _

             DayCnt(5) + DayCnt(6) + DayCnt(7)

Tom

[snedman]

SHA ZAM it worked. THANK YOU THANK YOU.

Snedman

[Yogi Anand]

Use the following array formula
{=SUM(IF(C9:C100>=C7,IF(C9:C100<=D7,1,0),0))}

where C7 houses 8:30 AM
and D7 houses 5:30 PM



_________________
Yogi Anand
Edit: Deleted reference to inactive web site from signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 18:36 ]

[Aladin Akyurek]

On 2002-05-06 10:45, snedman wrote:
TsTom has provided EXCELLENT assistance in making it easier to find all the data in the spreadsheet. I am looking for some assistance still in creating a formula that would calculate all calls that came in after 5:30 PM but before 8:30 AM (non-day specific)

The date column is B:9 down and the time column is C:9 down. I would need it to disregard blanks.
Any assistance would be greatly appreciated for this last one.

=COUNT(C9:C1066)-SUMPRODUCT((C9:C1066>F1)*(C9:C1066
where F1 houses 8:30 (that is: 8:30:00 AM) and F2 17:30 (that is: 5:30:00 PM).

It shouldn't be too difficult to do the same with COUNTIF (which is less expensive).



Exceptions Total: =COUNTIF(E9:E202,"X")+COUNTIF(E9:E202,"I")+COUNTIF(E9:E202,"C")+COUNTIF(E9:E202,"E")+COUNTIF(E9:E202,"V")

Given the long chain of COUNTIFs, I don't see any harm in using instead:

=SUMPRODUCT((E9:E202={"X","I","C","E","V"})+0)


Total Number of Calls: =COUNTIF(E9:E1066,"X")+COUNTIF(E9:E1066,"I")+COUNTIF(E9:E1066,"C")+COUNTIF(E9:E1066,"E")+COUNTIF(E9:E1066,"V")+COUNTIF(E9:E1066,"R")+COUNTIF(E9:E1066,"M")

Again by the above reasoning, I'd suggest:

=SUMPRODUCT((E9:E1066={"X","I","C","E","V","R","M"})+0)

Aladin

[snedman]

Done TsTOM thank you for that followup.

Thank you very much also IML. You guys are life savers.