Excel:Counting data

[ckelly0172]

Hi, I have 2 columns of data, column A with 10 different types of data and column B with 3 types of data. I wish to count how many of each combinations there are. However the problem lies at the fact that column A has a date and time associated with each of the 10 different type of data, therefore making each cell totally unique. I have tried a combination of COUNTIF and the AND function but am unable to use wildcards (* - as the date and time are not important) to detect certain values of column A.
Craig.

 

[GlennUK]

I think we need to see examples of column A data before being able to make suggestions.

Can you post some examples please.

Thanks, Glenn.

 

[ckelly0172]

Sure thing,

03/12/2003 17:49:36 Carl
03/12/2003 17:52:13 Carl
05/12/2003 17:17:45 Carl
07/12/2003 07:23:59 Bris
07/12/2003 08:04:49 Bris
07/12/2003 08:42:47 Uls
07/12/2003 17:28:14 Bris
08/12/2003 09:27:36 Bris
10/12/2003 15:37:33 Bord
12/12/2003 23:08:09 Bris
13/12/2003 06:36:34 Bord

Thanks

 

[xlbo]

Still not clear I'm afraid - can you explain what results you would want to see based on that data ?

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ckelly0172]

I'm sorry, i'll try and make it a little clearer, don't seem to be explaining myself that well!
If I have column A & B with this data:-
A B
03/12/2003 17:49:36 Carl E1
03/12/2003 17:52:13 Carl E2
05/12/2003 17:17:45 Carl E1
07/12/2003 07:23:59 Bris E1
07/12/2003 08:04:49 Bris E2
07/12/2003 08:42:47 Uls E2
07/12/2003 17:28:14 Bris E1
08/12/2003 09:27:36 Bris E2
10/12/2003 15:37:33 Bord E1
12/12/2003 23:08:09 Bris E1
13/12/2003 06:36:34 Bord E2

I would like to find out how many occurance of each combinations there are. eg carl & E1, bord & E1 etc. If there were no time and date included it would be quite simple, unfortunately i need to keep the time and date...
ta

 

[xlbo]

riiiight - now I get it - try this

=sumproduct((right(A1:A1000,4)="Carl&quot*(B1:B1000="E2&quot)

assuming data runs from A1:B1000

You will need to change the ,4 to whatever the length of the name is - if there are any names that aren't 4 characters long ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ckelly0172]

Geoff, thanks for this, I'm nearly there now... What does 'right' do, because i now have a similar problem with column B. Col B has data like 'MCCA/D Enc 7 EDC 1 (Teletext)' and I'm only interested in the 'Enc7 EDC1' bit, where Enc can be 7 or 8 and EDC can be 1, 2 or 3.
Much obliged for your help

 

[xlbo]

Please, please, please, when you are showing examples of data - show ALL the data. that way we don't have to keep guessing and giving you multiple answers

Given this string:
'MCCA/D Enc 7 EDC 1 (Teletext)'

Would the
Enc 7 EDC 1

always be the same number of characters from the left ?? - in this instance - 9

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ckelly0172]

My apologies Geoff, yes it would the be the same number of characters. I have sorted it now, it finally clicked after a littel thought.
Many thanks for your help
Craig

 

[xlbo]

no probs

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244