Frequency Question

[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

 

[Teethless mama]

Try this:

=SUMPRODUCT((A1:A10=6)/COUNTIF(B1:B10,B1:B10&""))

 

[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

 

[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!

 

[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!

 

[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.

 

[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 !
---------------------------------------------------------------------------

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.

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!

 

[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. 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)

 

[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!

 

[T. Valko]

You're welcome. Thanks for the feedback!