• 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.

Counting items in column A based on partial strings

P

Phuzzy

New Member
I am trying to count all the items in Column A if Column D contains a partial string and column E contains a different partial string.


So:


Column D contains a cell with AB CD EF GH

Column E contains a cell with FAST or SLOW


I want to count everything in column A only if column D contains EF and column E contains SLOW. And the strings could be in either upper or lower case.


Thanks,
 
Assuming there are 100 items in each column


=COUNTIFS(D2:D102,"EF",E2:E102,"Slow")


Case doesn't matter.


Edit to add that if EF or Slow are part of a larger word, then just change the criteria to "*EF*" and "*slow*" This way, you capture the variable regardless of its position in the string.
 
That did not work.

The formula I typed: =countifs(L2:L10000,"*DRS*",K2:K10000,"Billing*")

Results in a #NAME? error.


I wonder if it is because I am using excel 2003. If so, is there a work around?
 
Excel 2003 does not have Countifs. So I found this little nugget:


=SUMPRODUCT(--(L2:L10000="*DRS*"),--(K2:K10000="Billing*"))


Which now produces a result of 0. Which is not right at all.
 
Hi ,


Try using the following formula , entered as an array formula ( using CTRL SHIFT ENTER ) :


=SUMPRODUCT(--(IFERROR(FIND("DRS",L2:L10000)>0,0)),--(LEFT(K2:K10000,7)="Billing"))


Narayan
 
No. That produced a lovely @Name? error. There is no IFERROR function in 2003.


Thanks for trying though.
 
=SUMPRODUCT(--(ISNUMBER(SEARCH("EF",D2:D100))),--(ISNUMBER(SEARCH("SLOW",E2:E100))),A2:A100)


Search is the non-case sensitive version of FIND.
 
Luke. Nope the result is 0. I tried wildcarding strings in the formula but no.

I did figure out that I was extending the formula past the existing data and was getting an error. So fixing that I did get a result, the wrong one, but at least a result.


Thanks for trying.
 
Phuzzy,

Hmm. Extending past the range of data shouldn't have caused a problem since the ISNUMBER functions can handle an error and change it into a Boolean value. Are all cells in A2:A100 truly numbers (or blanks)?

The formula is working correctly for me. With this sample date

[pre]
Code: 
Col A	Ignore	Ignore	Col D	         Col E
1			this is effert	slow time
2			this is effert	slow time
3			not this	slow time
4			or this	        slow time
5			this is effert	slow time
[/pre]
My formula gives a result of 8 (1+2+5).

Can you confirm if your data is somehow different than what I have?
 
Luke


Column A is TEXT! Perhaps this make a difference. This SUMPRODUCT formula is a mystery to me.


Thanks
 
I am not trying to ADD column A, I am trying to COUNT column A if D and E meet the designated criteria.
 
Then perhaps we can ignore col A all together?

=SUMPRODUCT(--(ISNUMBER(SEARCH("EF",D2:D100))),--(ISNUMBER(SEARCH("SLOW",E2:E100))))


This would return a true count of how many rows have "EF" in col D and "Slow" in col E.

Check out one one the recent formula forensic articles on SUMPRODUCT:

http://chandoo.org/wp/2011/12/21/formula-forensics-no-007/
 
Luke,


Success! That seemed to have worked. Now the bigger question: Can this be done with 3 columns of criteria?


Thanks a ton, you made my life a LOT easier.
 
Figured it out! Three columns of data, no problem, even 4.

Sweet.

Thanks to all for the insight and help!


If Chandoo reads this: Thanks for the site. Over the past several days, I have become a big fan.
 
You must log in or register to reply here.
Back
Top