Hi,
See attchment. I need a formula on C1 that will total that contain "sp" from all different cells.
Thanks in advance.
Hi,
See attchment. I need a formula on C1 that will total that contain "sp" from all different cells.
Thanks in advance.
1. Please take a moment to check your Excel product/version and update your profile.
2. Try:
=SUMPRODUCT(--ISNUMBER(SEARCH("sp",B5:K14)))
3. Adjust the range (red) to suit your real data.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Glenn,
I am so sorry... I meant the total of numbers that contains with sp.
1sp + 2sp = 3.
On the sample attachment, the total should be 39?
Sorry and thanks in advance.
Please Login or Register to view this content.
You have not answered my question about Excel Product/version.
=SUM(IF(ISTEXT(B5:J14),--SUBSTITUTE(UPPER(B5:J14)," SP",""),0))
should do it. In your (mystery) version of Excel, this may/may not be an array formula, which needs CTRL-SHIFT-ENTER to get it working.
Hi Glenn,
It's windows 10.
It worked. Thank you so much.
Your OS is irrelevant. Its the Excel PRODUCT that may eeed updating.
Anyway - it works for you!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks