Count Numeric Value with condition

[Tendresse]

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

 

[David Biddulph]

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

 

[Tendresse]

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

 

[RagDyeR]

Try this *array* formula:

=COUNT(1/FREQUENCY(IF((B1:B100="Paul"),MATCH(A1:A100,A1:A100,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!
=====================================================

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

 

[Tendresse]

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]

Try this:

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

Also CSE entry.