Count Numeric Value with condition
 

I have a column 'A' that contains numeric and non-numeric data. I want to
count the number of numeric data in column 'A' when the adjacent cell in
column B is equal to a certain value. For example:
A B
12 Paul
13A Paul
3 Diane
5 Paul
In the example above the numeric values for Paul = 2.
How do i do that?!
Using Excel 2003.
Thank you in advance - Tendresse

Count Numeric Value with condition
 

=SUMPRODUCT(ISNUMBER(A1:A100)*(B1:B100="Paul"))
--
David Biddulph

"Tendresse" wrote in message
...
I have a column 'A' that contains numeric and non-numeric data. I want to
count the number of numeric data in column 'A' when the adjacent cell in
column B is equal to a certain value. For example:
A B
12 Paul
13A Paul
3 Diane
5 Paul
In the example above the numeric values for Paul = 2.
How do i do that?!
Using Excel 2003.
Thank you in advance - Tendresse

Count Numeric Value with condition
 

Brilliant ... thank you very much ..
Can I ask another question please?
How do i count the number of unique values in a range when the adjacent cell
has a certain value? For example:
A B
Apple Paul
Orange Paul
Apple Paul
Apple Diane

In this example, the number of unique values in column 'A' for 'Paul' is 2.

I'm using this formula to count the number of unique values in a range, but
i don't know how to add to it the condition of 'B:B100=Paul'
=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

Thanks again for all your help :)

"David Biddulph" wrote:

=SUMPRODUCT(ISNUMBER(A1:A100)*(B1:B100="Paul"))
--
David Biddulph

"Tendresse" wrote in message
...
I have a column 'A' that contains numeric and non-numeric data. I want to
count the number of numeric data in column 'A' when the adjacent cell in
column B is equal to a certain value. For example:
A B
12 Paul
13A Paul
3 Diane
5 Paul
In the example above the numeric values for Paul = 2.
How do i do that?!
Using Excel 2003.
Thank you in advance - Tendresse

Count Numeric Value with condition
 

Try this *array* formula:

=COUNT(1/FREQUENCY(IF((B1:B100="Paul"),MATCH(A1:A100,A1:A10 0,0)),ROW(1:100)))

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

Perhaps, for ease of revision, it's better to place the name you're looking
for into a cell, and then refer to that cell in the formula, say C1:

=COUNT(1/FREQUENCY(IF((B1:B100=C1),MATCH(A1:A100,A1:A100,0) ),ROW(1:100)))

Still CSE entered.
--

HTH,

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

"Tendresse" wrote in message
...
Brilliant ... thank you very much ..
Can I ask another question please?
How do i count the number of unique values in a range when the adjacent cell
has a certain value? For example:
A B
Apple Paul
Orange Paul
Apple Paul
Apple Diane

In this example, the number of unique values in column 'A' for 'Paul' is 2.

I'm using this formula to count the number of unique values in a range, but
i don't know how to add to it the condition of 'B:B100=Paul'
=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

Thanks again for all your help :)

"David Biddulph" wrote:

=SUMPRODUCT(ISNUMBER(A1:A100)*(B1:B100="Paul"))
--
David Biddulph

"Tendresse" wrote in message
...
I have a column 'A' that contains numeric and non-numeric data. I want to
count the number of numeric data in column 'A' when the adjacent cell in
column B is equal to a certain value. For example:
A B
12 Paul
13A Paul
3 Diane
5 Paul
In the example above the numeric values for Paul = 2.
How do i do that?!
Using Excel 2003.
Thank you in advance - Tendresse

Count Numeric Value with condition
 

Hi RagDyeR,
Thank you very much for your reply. It works very well but only if there are
no blank cells in the range A1:A100. if a cell in the range is blank, the
result of the formula is always 0.
Is there a way to say something like ignore blank cells?
Thanks a lot. :)

"RagDyeR" wrote:

Try this *array* formula:

=COUNT(1/FREQUENCY(IF((B1:B100="Paul"),MATCH(A1:A100,A1:A10 0,0)),ROW(1:100)))

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

Perhaps, for ease of revision, it's better to place the name you're looking
for into a cell, and then refer to that cell in the formula, say C1:

=COUNT(1/FREQUENCY(IF((B1:B100=C1),MATCH(A1:A100,A1:A100,0) ),ROW(1:100)))

Still CSE entered.
--

HTH,

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

"Tendresse" wrote in message
...
Brilliant ... thank you very much ..
Can I ask another question please?
How do i count the number of unique values in a range when the adjacent cell
has a certain value? For example:
A B
Apple Paul
Orange Paul
Apple Paul
Apple Diane

In this example, the number of unique values in column 'A' for 'Paul' is 2.

I'm using this formula to count the number of unique values in a range, but
i don't know how to add to it the condition of 'B:B100=Paul'
=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

Thanks again for all your help :)

"David Biddulph" wrote:

=SUMPRODUCT(ISNUMBER(A1:A100)*(B1:B100="Paul"))
--
David Biddulph

"Tendresse" wrote in message
...
I have a column 'A' that contains numeric and non-numeric data. I want to
count the number of numeric data in column 'A' when the adjacent cell in
column B is equal to a certain value. For example:
A B
12 Paul
13A Paul
3 Diane
5 Paul
In the example above the numeric values for Paul = 2.
How do i do that?!
Using Excel 2003.
Thank you in advance - Tendresse

Count Numeric Value with condition
 

Try this:

=COUNT(1/FREQUENCY(IF((B1:B100="Paul")*(A1:A100<""),MATCH( A1:A100,A1:A100,0
)),ROW(1:100)))

Also CSE entry.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tendresse" wrote in message
...
Hi RagDyeR,
Thank you very much for your reply. It works very well but only if there
are
no blank cells in the range A1:A100. if a cell in the range is blank, the
result of the formula is always 0.
Is there a way to say something like ignore blank cells?
Thanks a lot. :)

"RagDyeR" wrote:

Try this *array* formula:


=COUNT(1/FREQUENCY(IF((B1:B100="Paul"),MATCH(A1:A100,A1:A10 0,0)),ROW(1:100))
)

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

Perhaps, for ease of revision, it's better to place the name you're
looking
for into a cell, and then refer to that cell in the formula, say C1:


=COUNT(1/FREQUENCY(IF((B1:B100=C1),MATCH(A1:A100,A1:A100,0) ),ROW(1:100)))

Still CSE entered.
--

HTH,

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

"Tendresse" wrote in message
...
Brilliant ... thank you very much ..
Can I ask another question please?
How do i count the number of unique values in a range when the adjacent
cell
has a certain value? For example:
A B
Apple Paul
Orange Paul
Apple Paul
Apple Diane

In this example, the number of unique values in column 'A' for 'Paul' is
2.

I'm using this formula to count the number of unique values in a range,
but
i don't know how to add to it the condition of 'B:B100=Paul'
=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

Thanks again for all your help :)

"David Biddulph" wrote:

=SUMPRODUCT(ISNUMBER(A1:A100)*(B1:B100="Paul"))
--
David Biddulph

"Tendresse" wrote in message
...
I have a column 'A' that contains numeric and non-numeric data. I
want to
count the number of numeric data in column 'A' when the adjacent
cell in
column B is equal to a certain value. For example:
A B
12 Paul
13A Paul
3 Diane
5 Paul
In the example above the numeric values for Paul = 2.
How do i do that?!
Using Excel 2003.
Thank you in advance - Tendresse