Counting items within time ranges and containing text

[exceldummy]

Hi there! I'm using Excel 2002 and have the following trouble wit
counting items within time ranges

I have in one spreadsheet
Customer Name Aged time
Premiere & Sons Inc. 3:00:53
ABC Co. Inc. 23:10:00
Perfect Inc. 14:06:45
ABC Worldwide Inc. 48:00:20
Premiere Inc. 15:22:30
ABC Inc. 1:25:35

And on another sheet I want to have the following details.
Customer Reports >12h 12-24h 24-48h <48h
ABC 3 1 1 0
1
Perfect 1 0 1 0
0
Premiere 2 1 1 0
0

I was able to do the reports one by =COUNTIF('spreadsheet!A:A,"*ABC*"
but I don't seem to be able to get the aged summary. Can anyone hel
please… Thanks!!!

 

[Mike]

Which will you count "24:00:00" in, 12-24h or 24-48h ?

 

[Frank Kabel]

Hi
as you're testing for two conditions you have to use SUMPRODUCT. To
evaluate alle 'ABC company' reports below 12 hours use the following:
=SUMPRODUCT((FIND("ABC",'spreadsheet!A1:A999)>0)*('spreadsheet!B1:B999<
TIME(12,0,0))

HTH
Frank

 

[Bob Phillips]

Hi,

You need SUMPRPODUCT, but you need care, so I will give all the formulae
rather than just one

< 12 hors
=SUMPRODUCT(--(LEN(spreadsheet!$A$2:$A$20)>LEN(SUBSTITUTE(spreadsheet!$A$2:$
A$20,$A2,"")))*(spreadsheet!$B$2:$B$20<=0.5))
12-24
=SUMPRODUCT((LEN(spreadsheet!$A$2:$A$20)>LEN(SUBSTITUTE(spreadsheet!$A$2:$A$
20,$A2,"")))*(spreadsheet!$B$2:$B$20>0.5)*(spreadsheet!$B$2:$B$20<=1))
24-48
=SUMPRODUCT((LEN(spreadsheet!$A$2:$A$20)>LEN(SUBSTITUTE(spreadsheet!$A$2:$A$
20,$A2,"")))*(spreadsheet!$B$2:$B$20>1)*(spreadsheet!$B$2:$B$20<=2))

48

=SUMPRODUCT((LEN(spreadsheet!$A$2:$A$20)>LEN(SUBSTITUTE(spreadsheet!$A$2:$A$
20,$A2,"")))*(spreadsheet!$B$2:$B$20>2))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[exceldummy]

24:00:00 is in 24-28h, so the other one is 12-23:59:59

Luke