M
Moily
Hi there,
I have a spreadsheet with 7 columns (named Bird, Cow, Horse, Rabbit, Pig,
Cat, Dog) and 23 rows that have either yes or no in each cell - mainly no's.
If there is a 'yes' in Bird then there should be 'no' in each of the other 6
columns (Cow, Horse, Rabbit, Pig, Cat, Dog). Basically, if there is a 'yes'
in Bird then they have identified themselves as 'Bird' rather than any other
animal.
I then have 10 more columns (named ans1, ans2, ans3, etc) that each have
responses such as "A", "B", "C", "D", "E" in each column.
Essentially, I want to know how many Birds have chosen "A" as an answer in
'ans1'. I currently have a formula that can calculate how many 'yeses' in
the 'Bird' column have "A" in the 'ans1' column that goes as follows:
=SUMPRODUCT(($A$2:$A$23="Yes")*($H$2:$H$23="A"))
I currently use several variations of this formula to create a matrix that
shows how many "A", "B", "C", "D", "E" answers for each of the 7 animal
columns (basically determine how many birds chose each answer, how many cows
chose each answer, etc, etc).
Unfortunately, some animals seem to have a bit of a misunderstanding about
their parentage and think that they are both Birds AND Horses or Cows,
Rabbits AND Dogs or else consider themselves to be Whales and don't fit into
any category offered. Unfortunately this causes a bit of trouble with my
forumla as it now counts the mixed parentage animals as two or three separate
responses depending on how many columns have 'yeses' for the individual row.
I'd like to have a formula that recognizes only the first 'yes'. I.e. if an
animal has answered 'yes' to being a Cow, Rabbit AND Dog then the formula
will only count the 'ans1' response for the Cow 'yes' but not the Rabbit
'yes' or the Dog 'yes'. In this scenario my current formula counts that one
response as three separate ones: one for Cow, one for Rabbit and one for Dog.
Appreciate any help that is offered and hope I've been clear!!!
Best,
Ann
I have a spreadsheet with 7 columns (named Bird, Cow, Horse, Rabbit, Pig,
Cat, Dog) and 23 rows that have either yes or no in each cell - mainly no's.
If there is a 'yes' in Bird then there should be 'no' in each of the other 6
columns (Cow, Horse, Rabbit, Pig, Cat, Dog). Basically, if there is a 'yes'
in Bird then they have identified themselves as 'Bird' rather than any other
animal.
I then have 10 more columns (named ans1, ans2, ans3, etc) that each have
responses such as "A", "B", "C", "D", "E" in each column.
Essentially, I want to know how many Birds have chosen "A" as an answer in
'ans1'. I currently have a formula that can calculate how many 'yeses' in
the 'Bird' column have "A" in the 'ans1' column that goes as follows:
=SUMPRODUCT(($A$2:$A$23="Yes")*($H$2:$H$23="A"))
I currently use several variations of this formula to create a matrix that
shows how many "A", "B", "C", "D", "E" answers for each of the 7 animal
columns (basically determine how many birds chose each answer, how many cows
chose each answer, etc, etc).
Unfortunately, some animals seem to have a bit of a misunderstanding about
their parentage and think that they are both Birds AND Horses or Cows,
Rabbits AND Dogs or else consider themselves to be Whales and don't fit into
any category offered. Unfortunately this causes a bit of trouble with my
forumla as it now counts the mixed parentage animals as two or three separate
responses depending on how many columns have 'yeses' for the individual row.
I'd like to have a formula that recognizes only the first 'yes'. I.e. if an
animal has answered 'yes' to being a Cow, Rabbit AND Dog then the formula
will only count the 'ans1' response for the Cow 'yes' but not the Rabbit
'yes' or the Dog 'yes'. In this scenario my current formula counts that one
response as three separate ones: one for Cow, one for Rabbit and one for Dog.
Appreciate any help that is offered and hope I've been clear!!!
Best,
Ann