Setting multiple conditions to return a figure from multiple cells

[Sapper]

Hi,
A B
1 Male
2 Male 21/06/71
3 Female 08/11/68 #
4 Male 11/04/70
5 Female
6 Female 19/10/69 #

I want to count the number of females in A with a date against their name in
B and return as a number.
=Countif(A1:A6,"Female") returns 3. How can I tell excel to look at those 3
cells(without knowing their references) and count those with a date in the
adjacent cell ie 2?

Thank you

 

[Sheeloo]

In C3 enter the formula (after changing 100 to your last row)
=SUMPRODUCT(--(A1:A100="Female"),--(B1:B100>0))
or
=SUMPRODUCT(--(A1:A100=A3),--(B1:B100>0))

If you want to copy this down to other cells then change A to $A

 

[Sapper]

Hi sheelo

Thanks for help. Using your first example I get an error message, using the
second I get a "value" message.
What is the significance of the dashes(--) in the formula?
What is the significance of using A3 instead of "Female" in the second
formula?
I clearly have a lot to learn but my books dont go deep enough into subject.

Sapper

: my

 

[Sheeloo]

Download and see the file...
http://wikisend.com/download/583754/Sample.xls

With your data A1:A100="Female" gives you
{False,False,True,False,False,True}

-- converts the logival values to 1 or 0
--(A1:A100="Female") becomes
{0,0,1,0,0,1}

When you use A3 instead of "Female" the formula can give you flexibility of
copying the formula to other rows... Of course you will have to change A1:A6
to $A1:$A6 to refer to the same range in all rows

=SUMPRODUCT(--(A1:A100="Female"),--(B1:B100>0))

 

[Sapper]

Hi Sheeloo

I downloaded the sample, thank you.
I coudn't get the formula to work until I entered the date as text and then
it worked.
Many thanks for help
Sapper