-3
In Excel, I want to count the number of Y in column B if column A equals F. Does anyone know the code?
For example, in column A, I have people's gender. In column B, I have if they are pregnant or not (yes or no). I want to know the number of pregnant people among the females, so only if column A = female.
Paige Anderson
Posted 2016-07-05T14:16:08.847
Reputation: 23
1
If only women can be pregnant, below your data in column B, use the follwoing formula =countif(b2:b50,"yes"). Replace b2 and b50 with the appropriate range of cells, and replace "yes" with the actual word or truth value of those cells you wish to count.
A slower way to accomplish pretty much the same thing would be to make a third column of the truth value of "Female and Pregnant".
In C2, type =if(and(A2="F",b2="Pregnant"),TRUE,FALSE). Then, copy the formula downward. Then, below your data in column C, use the following formula
=countif([insert range of cells in column C to test and count],[enter the criteria, for example, TRUE]).
To use multiple criteria with an OR in-between, you can simply use the OR function. The or function returns true when any of the parameters are true.
wizlog
Posted 2016-07-05T14:16:08.847
Reputation: 12 320
What are you hiding us when you say "If only women can be pregnant"? ... In that arcane mystery named conception I maybe still missing something :-) – Hastur – 2016-07-05T15:06:10.813
@Hastur hahaha, you're right. Nowadays though, if the data is from a survey, a respondent might enter other or something else entirely for "gender" and can still be pregnant. – wizlog – 2016-07-05T15:08:22.983
Yep, I just didn't resist to make the joke... Btw a conditional format on the B column associated with the corresponding A value can spot eventual incongruity/error/special cases. – Hastur – 2016-07-05T15:19:51.070
3What did you already try? – Hennes – 2016-07-05T14:17:28.857
1Hint: COUNTIFS() – Scott Craner – 2016-07-05T14:17:46.543
Ok. So I did =COUNTIFS(E2:E209, "F", F2:F209, "yes") But what if I want to see if Column F equals "yes" or "Y" ? Some of them have yes recorded as "yes" and some have it as "Y" – Paige Anderson – 2016-07-05T14:41:40.583
For the or use this
=SUM(COUNTIFS(E:E,"F",F:F,{"yes","y"}))and instead of hitting enter to exit edit mode use Ctrl-Shift-Enter. If done correctly then Excel will put{}around the formula. – Scott Craner – 2016-07-05T15:00:10.430Free suggestion: add a conditional format for the cells of the column B when the cell is not empty (or it is set to
yes,y...) and the corresponding cell of the gender is notM, maybe changing the background colour, just to put it in evidence. – Hastur – 2016-07-05T15:14:07.387ok. One more question. What if I want to count the number of blank cells among females? As in I want to count the number of blank cells in column F if column E is "female". All in all, I want to know the number of pregnant females (got it) number of non-pregnant females (got it) and number of females with a blank answer (How?) – Paige Anderson – 2016-07-05T15:29:08.250
The criteria for empty cells is
"<>"– Scott Craner – 2016-07-05T16:20:04.010So, =SUM(COUNTIFS(E2:E209,"F",F2:F209,"<>")) <--not working – Paige Anderson – 2016-07-05T16:29:49.980
No remove the SUM() wrapper as you are only using one criteria. then it should work. If that does not work then you may have spaces in the field instead of a truly empty field. – Scott Craner – 2016-07-05T17:07:21.593