Counting items within time ranges and containing text

  • Thread starter Thread starter exceldummy
  • Start date Start date
E

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!!!
 
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
 
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
Click to expand...
=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)
 
Back
Top