Help with formula for counting occurances of text in collumns

**G188ONS** · 11-30-2009, 07:48 PM

Hi,

I'm currently at University collecting data for my dissertation and using Excel makes calculations much simpler and quicker, once you know how to use it. I have used Google Documents to create a form and then downloaded the data to Excel for analysis and have used the following formula to count the number of time people have answered yes to a question:

{=SUM(LEN(F2:F1000)-LEN(SUBSTITUTE(F2:F1000,"Yes","")))/LEN("Yes")}

All working fine there. However, I want to know if it is possible and if so how, I can create a formula to find of those people who have answered Yes to one question, then answered No to another. I know it doesn't work but along the lines of:

=SUM(LEN(F2:F1000)-LEN(SUBSTITUTE(F2:F1000,"Yes","")))/LEN("Yes") AND SUM(LEN(H2:H1000)-LEN(SUBSTITUTE(H2:H1000,"No","")))/LEN("No")

**Richard Buttrey** · 11-30-2009, 08:01 PM

Hi,

The formulae you are using are array formulae which are not particularly efficient in terms of speed. You'd be better using a helper column to test whether the two fields in question in each row are Yes and No, and then use a simple =SUM() to total the number of rows that meet the criteria.

For instance if A and B hold the Yes/No answers then use

Please Login or Register  to view this content.

Then =SUM(<helper column>)

HTH

**khamilton** · 11-30-2009, 08:05 PM

Maybe try something like this:=countifs(F2:F1000,"Yes",H2:H1000,"No")

**DonkeyOte** · 12-01-2009, 05:16 AM

If the strings are embedded (I assume that's why you're using the SUBSTITUTE array) then you can still use the COUNTIFS as illustrated (assuming as per profile you're using XL2007), however, you will need to utilise wildcards within the criteria, ie

=COUNTIFS(F2:F1000,"*Yes*",H2:H1000,"*No*")

of course the above, like your original, is still open to error should either term appear within a word, eg

H2: "Yes, It Snowed"

would still count as a No... if there is inconsitent punctuation (delimiters) then it can be hard to ensure you'll always get a 100% accurate result even if revert to a more sophisticated approach like SUMPRODUCT (which would allow you to manipulate the source values within the calculation itself).