Making COUNTIF conditional on the value in another field

  • Thread starter Thread starter rowenas82
  • Start date Start date
R

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!
 
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?
 
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?
 
Back
Top