Making COUNTIF conditional on the value in another field

[rowenas82]

HELP! I am trying to count the number of times a number appears in a field,
conditional upon it being a certain number in a different field. This is
tracking survey response data for different categories of services, eg; CE
(Careers & Employment), PC (Personal Counselling).

Eg; I need to know the number of times a "1" (Very dissatisfied) has been
recorded for a particular question for surveys completed under the Careers &
Employment category. Categories are recorded as abbrieviated (CE) in Column A.

SO, I want to record in cell B15 the number of times that the number "1"
appears in a set of data in cells B4:B38, ONLY when the corresponding cell in
A on the same row has the letters "CE" in it.

Can anyone help? Thank you!

 

[rowenas82]

Tried SUMPRODUCT (=SUMPRODUCT((Results!A4:A38="CE"),(Results!B4:B38="1"))
but it just came up with 0 which wasn't correct. And would this know to only
check the row that the 2nd criteria data is on, not all criteria?

 

[David Biddulph]

Firstly you may need to coerce the booleans to numerical, so change
=SUMPRODUCT((Results!A4:A38="CE"),(Results!B4:B38="1"))
to either
=SUMPRODUCT(--(Results!A4:A38="CE"),--(Results!B4:B38="1"))
or
=SUMPRODUCT((Results!A4:A38="CE")*(Results!B4:B38="1"))

Secondly, are you sure that you want to test column B against the text
string "1" and not the number 1?