Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making COUNTIF conditional on the value in another field
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making COUNTIF conditional on the value in another field
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? "rowenas82" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making COUNTIF conditional on the value in another field
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? -- David Biddulph "rowenas82" wrote in message ... 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? "rowenas82" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
making a cell a search/find field | Excel Discussion (Misc queries) | |||
Making a field value append a hyperlink | Excel Discussion (Misc queries) | |||
Making COUNTIF update in real time | Excel Worksheet Functions | |||
Making one field equal 2 others | Excel Worksheet Functions | |||
What is a field name in excel when making a pivot table? | Excel Discussion (Misc queries) |