Frequency Question
 

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

Frequency Question
 

Try this:

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


"Bigfoot17" wrote:

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

Frequency Question
 

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:B1 0&"",0)),MATCH(B1:B10&"",B1:B10&"",0))0))

ctrl+shift+enter, not just enter


"Bigfoot17" wrote:

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

Frequency Question
 

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!

Frequency Question
 

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

"Bigfoot17" wrote in message
...
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!

Frequency Question
 

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" wrote:

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

"Bigfoot17" wrote in message
...
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!

Frequency Question
 

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" wrote in message
...
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" wrote:

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

"Bigfoot17" wrote in message
...
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!

Frequency Question
 

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

Frequency Question
 

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(B 1:B10&"",B1:B10&"",0))0,J1:J10))

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

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
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!).

Frequency Question
 

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" wrote:

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(B 1:B10&"",B1:B10&"",0))0,J1:J10))

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

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
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!).

Frequency Question
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
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" wrote:

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(B 1:B10&"",B1:B10&"",0))0,J1:J10))

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

--
Biff
Microsoft Excel MVP


"Bigfoot17" wrote in message
...
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!).