Frequency Question

B

Bigfoot17

I am trying to count the unique numbers in COlumn B where the value in Column
A=6. I have figured out how to find the number of unique values in all of
Column B but not where the value in Column A=6. Any suggestions appreciated.

=SUM(IF(FREQUENCY(Log!$B$1:$B$25,Log!$B$1:$B$25)>0,1))


(A) (B) (C)
5 001
5 002
5 002
5 003
6 004
6 005
6 005
7
7 006
8 007
 
T

Teethless mama

Ignore my earlier reply. It doesn't give you correct result

Try this one.

=SUM(N(FREQUENCY(IF(A1:A10=6,MATCH(B1:B10&"",B1:B10&"",0)),MATCH(B1:B10&"",B1:B10&"",0))>0))

ctrl+shift+enter, not just enter
 
B

Bigfoot17

I was trying to edit my post because I sent it too soon. But thank you for
the reply - I have implemented it and it worked the way I needed, May I
follow up on this?

[1] While it worked it had a few things I need to understand "Why it Worked"
*Why the "N"?
*Why &""?
[2] How would I add another criteria such as in the month of Column C?
*(MONTH(Log!$C$1:$C$10)=9)*1?
(A) (B) (C)
5 001 9/29/08
5 002 9/28/08
5 002 8/15/08
5 003 8/19/08
6 004 9/27/08
6 005 9/27/08
6 005 9/27/08
7 9/27/08
7 006 9/28/08
8 007 8/15/08

Thanks so much for the help!
 
R

RagDyeR

Try this *array* formula for 2 variables:

=COUNT(1/FREQUENCY(IF((A1:A10=6)*(MONTH(C1:C10)=9),MATCH(B1:B10&"",B1:B10&"",0)),ROW(1:10)))

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

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I was trying to edit my post because I sent it too soon. But thank you for
the reply - I have implemented it and it worked the way I needed, May I
follow up on this?

[1] While it worked it had a few things I need to understand "Why it Worked"
*Why the "N"?
*Why &""?
[2] How would I add another criteria such as in the month of Column C?
*(MONTH(Log!$C$1:$C$10)=9)*1?
(A) (B) (C)
5 001 9/29/08
5 002 9/28/08
5 002 8/15/08
5 003 8/19/08
6 004 9/27/08
6 005 9/27/08
6 005 9/27/08
7 9/27/08
7 006 9/28/08
8 007 8/15/08

Thanks so much for the help!
 
B

Bigfoot17

I having some problems applying the formula you suggest, it seems to get hung
on the ROW portion. Here is what my formula looks like (I am compounding it
by trying to enter the formula on one sheet and pull data from the "Log" tab
and the month to be checked for from the Sheet1 tab
=count(1/FREQUENCY(IF((Log!$A$6:$A$500=I4)*(Month(Log!$E6:$E500)=Sheet1!$F$2,MATCH(Log!$B$6:$B$500&"",Log!$B$6:$B$500&"",0,(ROW 6:500)))
(Log1!ROW 6:500) is not accepted either.

Any guidance is very much appreciated.
 
R

RagDyer

Try:
Row(1:495)

Which represents the number of rows in the array, *not* the location.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Bigfoot17 said:
I having some problems applying the formula you suggest, it seems to get
hung
on the ROW portion. Here is what my formula looks like (I am compounding
it
by trying to enter the formula on one sheet and pull data from the "Log"
tab
and the month to be checked for from the Sheet1 tab.
=count(1/FREQUENCY(IF((Log!$A$6:$A$500=I4)*(Month(Log!$E6:$E500)=Sheet1!$F$2,MATCH(Log!$B$6:$B$500&"",Log!$B$6:$B$500&"",0,(ROW
6:500)))
(Log1!ROW 6:500) is not accepted either.

Any guidance is very much appreciated.

RagDyeR said:
Try this *array* formula for 2 variables:

=COUNT(1/FREQUENCY(IF((A1:A10=6)*(MONTH(C1:C10)=9),MATCH(B1:B10&"",B1:B10&"",0)),ROW(1:10)))

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

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I was trying to edit my post because I sent it too soon. But thank you
for
the reply - I have implemented it and it worked the way I needed, May I
follow up on this?

[1] While it worked it had a few things I need to understand "Why it
Worked"
*Why the "N"?
*Why &""?
[2] How would I add another criteria such as in the month of Column C?
*(MONTH(Log!$C$1:$C$10)=9)*1?
(A) (B) (C)
5 001 9/29/08
5 002 9/28/08
5 002 8/15/08
5 003 8/19/08
6 004 9/27/08
6 005 9/27/08
6 005 9/27/08
7 9/27/08
7 006 9/28/08
8 007 8/15/08

Thanks so much for the help!
 
B

Bigfoot17

Thnaks it took awhile. It turns out that I was missing a close parenthesis
elsewhere in my formula.

My poject just got a lot more complicated for me as I need another variable
thrown in. I need the sum of J column for the unique values in B column when
A column equals 5 and the month in G column equals 9. Anyone care to take a
shot at that? (My head hurts!).
 
T

T. Valko

the month in G column equals 9

Assuming that column G contains DATES and not just the number 9

Try this array formula** :

=SUM(IF(FREQUENCY(IF((A1:A10=5)*(MONTH(G1:G10)=9)*(B1:B10<>""),MATCH(B1:B10&"",B1:B10&"",0)),MATCH(B1:B10&"",B1:B10&"",0))>0,J1:J10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
B

Bigfoot17

This worked first time, thank you. I am so glad for the assistance that is
available here. Each of these responses has been unbelievably helpful . . .
and the project moves forward!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top