Tough One, trying to count occurences of string based on string in separate column

[Rick]

Hi,

For every occurrence of a string in Column A, say "question2", I'd like to
count the number of times the string "satisfied" appears in Column B for
the same row.

The answer would be 1 for the following data. i.e. "satisfied" occurs "1"
time for all of "question2's. I would like to be able to add data and sort
it and the formula still work. I would locate the data on a separate
worksheet from the output of the formula.

question1 not-satisfied
question2 satisifed
question1 not-satisfied
question2 not-satisfied
question1 satisfied
question2 not-satisfied

Thank-you,

Rick
(e-mail address removed) <==remove XX for correct email address

 

[RagDyer]

List in sheet 2 -
Formula on sheet 1 -
=SUMPRODUCT((Sheet2!A1:A20="question2")*(Sheet2!B1:B20="satisfied"))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi,

For every occurrence of a string in Column A, say "question2", I'd like to
count the number of times the string "satisfied" appears in Column B for
the same row.

The answer would be 1 for the following data. i.e. "satisfied" occurs "1"
time for all of "question2's. I would like to be able to add data and sort
it and the formula still work. I would locate the data on a separate
worksheet from the output of the formula.

question1 not-satisfied
question2 satisifed
question1 not-satisfied
question2 not-satisfied
question1 satisfied
question2 not-satisfied

Thank-you,

Rick
(e-mail address removed) <==remove XX for correct email address

 

[Bob Phillips]

Hi Rick,

=SUMPRODUCT((A1:A100="question2")*(B1:B100="satisfied"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rick]

Works Great. Thank-you very much.
Rick