Criteria count
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.
|