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")
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
Then =SUM(<helper column>)Code:IF(AND(A1="Yes",B1="No"),1,0)
HTH
Richard Buttrey
If this was useful then please rate it appropriately.
Click the small star iconat the bottom left of my post.
Maybe try something like this:=countifs(F2:F1000,"Yes",H2:H1000,"No")
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).
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks