I'm having a major headache over a criteria count that I cannot figure out...
Maybe someone would have a solution for me.

I have two columns of data.
A1:A50 contains time slots that may repeat an undefinite amount of times.
B1:B50 contains various names that may or may not repeat

I need to count the number of different names that appears for a specific
time slots.

Here's an example:
7:00 Apple
7:00 Apple
7:00 Carrot
7:00 Peach
7:30 Cake
7:30 Carrot
7:30 Cake

Results should be:
7:00 3
7:30 2

I was thinking that an array formula would do the trick but I just can't
figure out how do it...

Thanks for any help you may provide me with.

I did not specify in my previous post but there is no predefined list of
items for column B.

Thanks.

"Gaetan" wrote:

I'm having a major headache over a criteria count that I cannot figure out...
Maybe someone would have a solution for me.

I have two columns of data.
A1:A50 contains time slots that may repeat an undefinite amount of times.
B1:B50 contains various names that may or may not repeat

I need to count the number of different names that appears for a specific
time slots.

Here's an example:
7:00 Apple
7:00 Apple
7:00 Carrot
7:00 Peach
7:30 Cake
7:30 Carrot
7:30 Cake

Results should be:
7:00 3
7:30 2

I was thinking that an array formula would do the trick but I just can't
figure out how do it...

Thanks for any help you may provide me with.

I would do a pivot table. Just make sure you place headings above each
column; for example time and item. Then create a pivot table with time as the
row and item as the data.


"Gaetan" wrote:

I'm having a major headache over a criteria count that I cannot figure out...
Maybe someone would have a solution for me.

I have two columns of data.
A1:A50 contains time slots that may repeat an undefinite amount of times.
B1:B50 contains various names that may or may not repeat

I need to count the number of different names that appears for a specific
time slots.

Here's an example:
7:00 Apple
7:00 Apple
7:00 Carrot
7:00 Peach
7:30 Cake
7:30 Carrot
7:30 Cake

Results should be:
7:00 3
7:30 2

I was thinking that an array formula would do the trick but I just can't
figure out how do it...

Thanks for any help you may provide me with.

Maybe this:

=IF(D1=TRUE,TRUE,IF(COUNTIF(A1:C1,FALSE)0,FALSE,T RUE))

HTH,
Paul

"Gaetan" wrote in message
...
I'm having a major headache over a criteria count that I cannot figure
out...
Maybe someone would have a solution for me.

I have two columns of data.
A1:A50 contains time slots that may repeat an undefinite amount of times.
B1:B50 contains various names that may or may not repeat

I need to count the number of different names that appears for a specific
time slots.

Here's an example:
7:00 Apple
7:00 Apple
7:00 Carrot
7:00 Peach
7:30 Cake
7:30 Carrot
7:30 Cake

Results should be:
7:00 3
7:30 2

I was thinking that an array formula would do the trick but I just can't
figure out how do it...

Thanks for any help you may provide me with.

Sorry....Wrong thread.

"PCLIVE" wrote in message
...
Maybe this:

=IF(D1=TRUE,TRUE,IF(COUNTIF(A1:C1,FALSE)0,FALSE,T RUE))

HTH,
Paul

"Gaetan" wrote in message
...
I'm having a major headache over a criteria count that I cannot figure
out...
Maybe someone would have a solution for me.

I have two columns of data.
A1:A50 contains time slots that may repeat an undefinite amount of times.
B1:B50 contains various names that may or may not repeat

I need to count the number of different names that appears for a specific
time slots.

Here's an example:
7:00 Apple
7:00 Apple
7:00 Carrot
7:00 Peach
7:30 Cake
7:30 Carrot
7:30 Cake

Results should be:
7:00 3
7:30 2

I was thinking that an array formula would do the trick but I just can't
figure out how do it...

Thanks for any help you may provide me with.

First, to create a list of *unique* times, say in C1 you enter
=A1
Then, enter this *array* formula in C2:
=IF(ISERR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$50&""),0) ),"",INDEX(IF(ISBLANK($A$1:$A$50),"",$A$1:$A$50),M ATCH(0,COUNTIF(C$1:C1,$A$1:$A$50&""),0)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy the formula down Column C until you get a blank
cell, meaning you've got all the unique times displayed.

NOW, using the unique time list as the criteria, enter this *array* formula
in D1:

=COUNT(1/FREQUENCY(IF((A$1:A$50=C1),MATCH(B$1:B$50,B$1:B$50 ,0)),ROW($1:$50)))

*After* the CSE entry, copy the formula down Column D as far as you have
unique times in Column C.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gaetan" wrote in message
...
I did not specify in my previous post but there is no predefined list of
items for column B.

Thanks.

"Gaetan" wrote:

I'm having a major headache over a criteria count that I cannot figure
out...
Maybe someone would have a solution for me.

I have two columns of data.
A1:A50 contains time slots that may repeat an undefinite amount of times.
B1:B50 contains various names that may or may not repeat

I need to count the number of different names that appears for a specific
time slots.

Here's an example:
7:00 Apple
7:00 Apple
7:00 Carrot
7:00 Peach
7:30 Cake
7:30 Carrot
7:30 Cake

Results should be:
7:00 3
7:30 2

I was thinking that an array formula would do the trick but I just can't
figure out how do it...

Thanks for any help you may provide me with.

Assuming no blank cells in between the ranges

"times" is a define name range A1:A50
"names" is a define name range B1:B50

In C1:
=IF(ISERR(SMALL(IF(FREQUENCY(MATCH(times,times,0), MATCH(times,times,0))0,ROW(INDIRECT("1:"&ROWS(tim es)))),ROWS($1:1))),"",INDEX(times,SMALL(IF(FREQUE NCY(MATCH(times,times,0),MATCH(times,times,0))0,R OW(INDIRECT("1:"&ROWS(times)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down until you see blank

In D1:
=SUM(N(FREQUENCY(IF(times=C1,MATCH(names,names,0)) ,MATCH(names,names,0))0))

ctrl+shift+enter, not just enter
copy down


"Gaetan" wrote:

I'm having a major headache over a criteria count that I cannot figure out...
Maybe someone would have a solution for me.

I have two columns of data.
A1:A50 contains time slots that may repeat an undefinite amount of times.
B1:B50 contains various names that may or may not repeat

I need to count the number of different names that appears for a specific
time slots.

Here's an example:
7:00 Apple
7:00 Apple
7:00 Carrot
7:00 Peach
7:30 Cake
7:30 Carrot
7:30 Cake

Results should be:
7:00 3
7:30 2

I was thinking that an array formula would do the trick but I just can't
figure out how do it...

Thanks for any help you may provide me with.

Wow! This works great and does exactly what I need it to do.

The problem is that the data I provided was as an example and the real data
is in the following location: C10:C116 and F10:F116. If I copy your formula
in O10 and P10 (where I need them at the end of my document), the formulas
are not working and I get a #N/A error.

I've tried to change part of the formula but it's so overwhelming that I
can't figure it out. Can you help me adapt it?

Thanks again.

"Teethless mama" wrote:

Assuming no blank cells in between the ranges

"times" is a define name range A1:A50
"names" is a define name range B1:B50

In C1:
=IF(ISERR(SMALL(IF(FREQUENCY(MATCH(times,times,0), MATCH(times,times,0))0,ROW(INDIRECT("1:"&ROWS(tim es)))),ROWS($1:1))),"",INDEX(times,SMALL(IF(FREQUE NCY(MATCH(times,times,0),MATCH(times,times,0))0,R OW(INDIRECT("1:"&ROWS(times)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down until you see blank

In D1:
=SUM(N(FREQUENCY(IF(times=C1,MATCH(names,names,0)) ,MATCH(names,names,0))0))

ctrl+shift+enter, not just enter
copy down


"Gaetan" wrote:

I'm having a major headache over a criteria count that I cannot figure out...
Maybe someone would have a solution for me.

I have two columns of data.
A1:A50 contains time slots that may repeat an undefinite amount of times.
B1:B50 contains various names that may or may not repeat

I need to count the number of different names that appears for a specific
time slots.

Here's an example:
7:00 Apple
7:00 Apple
7:00 Carrot
7:00 Peach
7:30 Cake
7:30 Carrot
7:30 Cake

Results should be:
7:00 3
7:30 2

I was thinking that an array formula would do the trick but I just can't
figure out how do it...

Thanks for any help you may provide me with.