Setting multiple conditions to return a figure from multiple cells
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 |
Setting multiple conditions to return a figure from multiple cells
In C3 enter the formula (after changing 100 to your last row)
=SUMPRODUCT(--(A1:A100="Female"),--(B1:B1000)) or =SUMPRODUCT(--(A1:A100=A3),--(B1:B1000)) If you want to copy this down to other cells then change A to $A "Sapper" wrote: 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 |
Setting multiple conditions to return a figure from multiple cells
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 "Sapper" wrote: my 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 |
Setting multiple conditions to return a figure from multiple c
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:B1000)) "Sapper" wrote: 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 "Sapper" wrote: my 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 |
Setting multiple conditions to return a figure from multiple c
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 |
All times are GMT +1. The time now is 10:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com