• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help Counting in Array

P

priamlau

New Member
Hello


My first post!


For example, I have


Collumn E


1 Apple;#Change in Scope;#Late placement

2 Change in Scope;#Unrealistic;#Customer Not

3 Change in Scope

4 Change in Scope;#Late placement

5 Customer Not Ready

6 Change in Scope;#Unrealistic date;#Late placement

7 hello;#Test placement


There are 15 words in the list (E1:E7). I want to count the number of words that does not match to be "Change in Scope" or "Late Placement" in the list


Is it possible


thanks for your help
 
How's this?

=SUMPRODUCT(1*((ISNUMBER(SEARCH("Change in Scope",E1:E7))+ISNUMBER(SEARCH("Late Placement",E1:E7)))=0))


Note that you said 15 words, but XL only looks at cells. If you want to look at 15 words, I recommend doing a text to columns to split the data into more cells. You can then change the ranges in the formula to match.
 
thanks for your repply Luke


I can use =SUMPRODUCT(--(ISNUMBER(SEARCH({"Change in Scope"},All_Results!E2:E9)))) to find the number of words matching "Change in Scope". In the spreadsheet, Words are divided by ;# between each other

Is it possible to create a formula that trim the words together placed betwwen ;#, then remove ;# making a space between words and then - (subtract) the number of instances "Chance in Scope" and "Late placement" appear?
 
Hi Priamlau,

If I understand your question, you are trying to count the number of words in the list E1:E7 (with "words" being separated by the delimited ";#") that do not match "Change in Scope" or "Late Placement".


Building on what Luke M suggested above, you may want to use the following formula to get the desired count of words:

=SUMPRODUCT((((LEN($E$1:$E$7)-LEN(SUBSTITUTE($E$1:$E$7,";#","")))/LEN(";#"))+1) - (ISNUMBER(SEARCH("Change in Scope",$E$1:$E$7)) + ISNUMBER(SEARCH("Late Placement",$E$1:$E$7))))


For the data set you supplied, I got 7 as the count, when using the above formula.


Cheers,

Sajan.
 
thanks sthomas, it worked great


Now got other issue, How to only count to cells that are not blank?


thanks


Priam
 
Thanks sirJB7


I found out that I have more rows which some are blank from E8:E800. From the formula provided by sthomas and luke is it possible to add counta at the start so it does not count blank cells? With the bellow formula it counts +1 for every row blank


this is the formula provided


=SUMPRODUCT((((LEN($E$1:$E$7)-LEN(SUBSTITUTE($E$1:$E$7,";#","")))/LEN(";#"))+1) - (ISNUMBER(SEARCH("Change in Scope",$E$1:$E$7)) + ISNUMBER(SEARCH("Late Placement",$E$1:$E$7))))


thank you
 
Taking a simple approach:

=SUMPRODUCT((((LEN($E$1:$E$7)-LEN(SUBSTITUTE($E$1:$E$7,";#","")))/LEN(";#"))+1) - (ISNUMBER(SEARCH("Change in Scope",$E$1:$E$7)) + ISNUMBER(SEARCH("Late Placement",$E$1:$E$7))))-COUNTBLANK(E1:E7)
 
You must log in or register to reply here.
Back
Top