I am a Call Center Manager and I need to trend work loads so I can
accurately staff my phone lines. I exported the Help Desk data as html from
my software and imported it into Excel. I then split the time by using the
text to column function. So all the times were clipped to two digits. For
example: 07:00 to 07:59 all show as 07. So now even if they look like
numbers, Excel treats them as text. My time frames are located in column D
and the submission type (Phone, Web, IM,etc) are located in column F. I need
to build tables that will automatically count each submission type by
military time frame going from 00-23. I have tried using countifs and
subproduct to no avail. I am thinking that a conditional statement like IF
D1 is equal to €œ07€� and F1 is equal to €œPhone€�, count this match as 1. My
formulas are not returning any values and I am not sure why. I tried these:

=COUNTIF(D1:D10001,"07")+COUNTIF(F1:F10001,"Web")
=SUMPRODUCT(--(D1:D100="07"),--(F2:F10001="Web"))

Any suggestions? My Excel background is basically more on the financial
side so I feel kinda stupid that I cant figure this one out.

=SUMPRODUCT(--(D1:D10001="07"),--(F2:F10001="Web"))
is the right formula... it should give you the number of rows which have 07
in Col D and Web in Col F
You need to change 10001 to the last row in your data...

Both ranges should be same... You have shown 100 and 10001 below... is that
a typo?

Also make you sure 07 is being treated as TEXT? Pl. test with ISNUMBER and
ISTEXT.

You can test by using the formula
=SUMPRODUCT(--(D1:D10001=D1),--(F2:F10001="Web"))

assuming D1 has 07... with this it won't matter what the cell actually
contains...

"Debbi" wrote:

I am a Call Center Manager and I need to trend work loads so I can
accurately staff my phone lines. I exported the Help Desk data as html from
my software and imported it into Excel. I then split the time by using the
text to column function. So all the times were clipped to two digits. For
example: 07:00 to 07:59 all show as 07. So now even if they look like
numbers, Excel treats them as text. My time frames are located in column D
and the submission type (Phone, Web, IM,etc) are located in column F. I need
to build tables that will automatically count each submission type by
military time frame going from 00-23. I have tried using countifs and
subproduct to no avail. I am thinking that a conditional statement like IF
D1 is equal to €œ07€� and F1 is equal to €œPhone€�, count this match as 1. My
formulas are not returning any values and I am not sure why. I tried these:

=COUNTIF(D1:D10001,"07")+COUNTIF(F1:F10001,"Web")
=SUMPRODUCT(--(D1:D100="07"),--(F2:F10001="Web"))

Any suggestions? My Excel background is basically more on the financial
side so I feel kinda stupid that I cant figure this one out.