If cell in row starts with "A" then sum right 1 number

[tlalbritton03]

I have searched and looked all over the internet and this site but can't figure out how to make this work.

I was given a tracking spreadsheet that I want to automate counts. Each row has the 14 days of the 2 week payperiod beginning in D9 through Q9:

SUN

MON

TUE

WED

THU

FRI

SAT

SUN

MON

TUE

WED

THU

FRI

SAT

<tbody>
</tbody>

Previously entered data would be such as under Monday A-9, Tuesday A-9, Wed S-4, etc. in D10:Q10 and continuing.

SUN

MON

TUE

WED

THU

FRI

SAT

SUN

MON

TUE

WED

THU

FRI

SAT

A - 9

A-9

S-4

H

S-9

<tbody>
</tbody>

I want to sum the rightmost number for each such that I have a sum for A of 18 and another cell has a sum of 13 for S. I've looked at SUMIF, SUMIFS, LEFT, RIGHT, IF, IF THEN etc but can't seem to work this out. It seems to be the combination of checking for the left most character to sum the rightmost character is throwing a glitch.

I would appreciate any help you can provide. Thank You.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this, copied across and down.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
9	A	S	SUN	MON	TUE	WED	THU	FRI	SAT	SUN	MON	TUE	WED	THU	FRI	SAT
10	18	13		A - 9	A-9	S-4					H		S-9
11	0	16			S-10				S - 6
Sum Right

Cell Formulas
Range	Formula
B10	=SUMPRODUCT(--(LEFT($D10:$Q10,1)=B$9),--MID(SUBSTITUTE($D10:$Q10&"-0","-",REPT(" ",10)),10,10))

 

[alansidman]

How many different Alpha prefixes will there be? Identify them for us. I only see a VBA solution for this. Where would the sums be placed once determined? How many lines of data in an average spreadsheet?

 

[Rick Rothstein]

I only see a VBA solution for this.

Did you see Peter's formula solution in Message #2 yet?

By the way, here is another formula (an array-entered** one) the OP can consider (assumes the same layout as Peter did)...

=SUM(-IF(LEFT($D10:$Q10)=B$9,MID($D10:$Q10,2,15)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[alansidman]

Thank you Rick. I had not seen that when I posted. Timing of post was only minutes later and with Forum upgrade, you no longer see what you have posted in the grid until next person posts. So this is the first time to see Peter's post. As a moderator, suggest you advise Admin that this is not a good practice.

 

[Peter_SSs]

.. you no longer see what you have posted in the grid until next person posts.

Am I understanding that correctly?
You reply to a thread and you are not returned immediately to viewing that thread? That is, you do not even see your own post after you submit it? Neither of those is my circumstance.

 

[tlalbritton03]

Thank You! THANK YOU! Both Peter and Rick's solutions work perfectly. Can you give an over view of what the formula does so I can learn how it works? Particularly I am interested in your use of MID, Rick's use of the -IF (minus IF?) I have worked to figure this out for about 4 days and you do it in an hour or two. Thank You again

 

[Rick Rothstein]

Rick's use of the -IF (minus IF?)

The MID function is removing the A when that is the first letter... what remains is a minus sign followed by a number or a space and then a number. The SUM function adds up all those negative values and the minus sign in front of the IF, in effect, cancels out the negative sum. I am not sure now why I wrote it that way instead of like this (which is probably a more normal way to do it)...

=SUM(IF(LEFT($D10:$Q10)=B$9,-MID($D10:$Q10,2,15)))

Notice the minus sign is not in front of the MID function instead of in front of the IF so that it directly cancels out the minus sign that remains after removing the leading letter A.

 

[alansidman]

@Peter,
No. When I return to the grid listing new posts, I do not see the posting for that thread. Usually, after I post, I do not look to see if someone has posted in front of me and in this case did not see your post until Rick pointed out that you had posted a formula solution.

I did like it better when the forum, listed updates to threads when you return to the forum listings.

At the end of the day, it is good that both you and Rick have satisfied the OPs requirements even though I did not see that as a possible solution. The great thing about Excel is that often there are multiple different solutions to the same issue. Hope you are having a good day.

Alan

 

[tlalbritton03]

The MID function is removing the A when that is the first letter... what remains is a minus sign followed by a number or a space and then a number. The SUM function adds up all those negative values and the minus sign in front of the IF, in effect, cancels out the negative sum. I am not sure now why I wrote it that way instead of like this (which is probably a more normal way to do it)...

=SUM(IF(LEFT($D10:$Q10)=B$9,-MID($D10:$Q10,2,15)))

Notice the minus sign is not in front of the MID function instead of in front of the IF so that it directly cancels out the minus sign that remains after removing the leading letter A.

I had never considered using the dash as a minus and using the double negative. If I may say, quite an elegant solution and I learned a couple of things. Thank You very much.