Hi all, I need to count a cell if it contains a word, in this case "Complete". However, it may also be written in upper/lower case and also written as "Completed". There may also be other text in the cell. How can I search for the letters "complete" regardless of case and other text and count it?
Hi,
maybe it could of some help
Formula:
Please Login or Register to view this content.
Regards
Last edited by canapone; 10-31-2014 at 04:51 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Hi again
to count how many strings contain "complete" in A1:A100
Formula:Please Login or Register to view this content.
Regards
Excellent thank you both!!
Hi,
you're welcome.
Thanks for providing feedback.
Cheers
I d have one further challenge, I need to add this sumproduct function inside a sumif.
SumProduct - =SUMPRODUCT(--(ISNUMBER(SEARCH("complete",V6:AJ6)))) counts the "Complete"
SumIF - is to count a range of days it took to complete each task, so the formula returns and sums all days in range X5:AJ5 where completed is found. I am unable to put these two together.
=SUMIF(V6:AJ6,"completed",$X$5:$AJ$5) works however it doesn't address the issue of case, different spellings and other text.
Hi again,
maybe you can simplify formulae.
Instead of SUMPRODUCT to count:
Please Login or Register to view this content.
to sum up values in $X$5:$AJ$5
Not sure: I can adjust formulae.Please Login or Register to view this content.
Cheers
Last edited by canapone; 10-31-2014 at 05:12 AM.
That's awesome thank you so much!! Honestly I tried wildcards and had a #Value error so I must have been looking up the wrong range. Thank you s much!!!
Hi again
thanks for ****feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks