To Sum a range containing 3 text Characters then a number

Hi,

I have struggled all day on this, in 1 column (B) I have a 10 rows of data containing initials then a number, like AAA8. I have a cell in A1 that contains the initials I want to query, like AAA.

How can I sum all the numbers together (B1-B10), they may be present multiple times in the list. In this example I want the Number 9.

A B
1 aaa aaa6
2 bbb3
3 aaa3
4 ccc1
5 ddd3
6 ggg5
7 hgh5
8 hsb2
9 sjs3
10 sns4

=SUMPRODUCT((0&SUBSTITUTE(B1:B10,A1,""))+0) This works if all the initals in B1-B10 start with AAA.

I have tried this =IF(LEFT(B1,3)=A1,((MID(B1,4,99))+0),0) but this only works for one row B1

If someone can take a look and advise that would be great.

This array* formula will do it:

=SUM(IF(LEFT(B1:B10,LEN(A$1))=A$1,--SUBSTITUTE(B1:B10,A$1,"")))

*An array formula needs to be committed using the keystrokes Ctrl-Shift-Enter (CSE), rather than the usual <Enter>

Hope this helps.

Pete

Try this.
Confirmed with ctrl+shift+enter (Not just enter)

Formula:

Please Login or Register  to view this content.

Thank you so much! Never used array formulaes, are they as stable as normal ones?

One side effect that if no initals are matched, how can i put a 0 value in.

Thank you

Thank you zbor, your method achieves this thank you all.

Yes, they are stable.
They just evaluate whole array, rather than just one cell.

Like SUMPRODUCT for example.
It's also array function (only you don't need to enter it with ctrl+shift+enter)

I have spend the whole day on this and you have solved it in minutes!! and I wonder if you could assist in the next step to finish it off.

Instead of 1 column containing 10 rows, I actually have 365 columns. 1 for each day of year. B1 is 1/1/2015. B2-B11 has the rows of initials. B2 is 2/1/2015 etc

The formula solves the value per day depending on initial how can i search the whole year between two dates say A2 with a start date and A3 with an end date.


This works for a row but cant figure out how to include the formuale just given.

=(COUNTIFS(standby,A1,dateyear,">="&$a$2,dateyear,"<="&$a$3)

A1 contains the initials like aaa
dateyear is the range of dates from 1/1/2015 to 31/12/2015
standby is the range of the whole row that I wish to use your formaula.


Thank you

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Thank you for your time. Hopefully attached is the spreadsheet back to bare bones.

It's password protected.

I am so sorry. Here the file is again.

Help.xlsx

Just a not that in above solution is one criteria redundant so it should rather be:

Formula:

Please Login or Register  to view this content.

And in attachemnt are both solution: mine and benishiryo

Formula:

Please Login or Register  to view this content.

They are different approaches so you might get different result in some different occasions (like there is no date from C3 in dateh1 or something).
In that case mine would return 0 while benishiryo would still work.

But now both return same value

Thank you benishiryo and zbor so much for the help you have given. That works a treat.

Hi zbor

I think I will use yours as sometime there may be a blank value in the rows of data. Yours works where as benishiryo comes up with a value error.

However his is case insensitive which is great. Can your formulae be modified to work if initials are entered in upper or lowercase?

Thank you again in advance.

Chris