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

tlalbritton03

New Member
Joined
Oct 1, 2017
Messages
3
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:
[TABLE="width: 602"]
<tbody>[TR]
[TD="class: xl64, width: 43"]SUN[/TD]
[TD="class: xl64, width: 43"]MON[/TD]
[TD="class: xl64, width: 43"]TUE[/TD]
[TD="class: xl64, width: 43"]WED[/TD]
[TD="class: xl64, width: 43"]THU[/TD]
[TD="class: xl64, width: 43"]FRI[/TD]
[TD="class: xl64, width: 43"]SAT[/TD]
[TD="class: xl64, width: 43"]SUN[/TD]
[TD="class: xl64, width: 43"]MON[/TD]
[TD="class: xl64, width: 43"]TUE[/TD]
[TD="class: xl64, width: 43"]WED[/TD]
[TD="class: xl64, width: 43"]THU[/TD]
[TD="class: xl64, width: 43"]FRI[/TD]
[TD="class: xl64, width: 43"]SAT[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 602"]
<tbody>[TR]
[TD]SUN[/TD]
[TD]MON[/TD]
[TD]TUE[/TD]
[TD]WED[/TD]
[TD]THU[/TD]
[TD]FRI[/TD]
[TD]SAT[/TD]
[TD]SUN[/TD]
[TD]MON[/TD]
[TD]TUE[/TD]
[TD]WED[/TD]
[TD]THU[/TD]
[TD]FRI[/TD]
[TD]SAT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A - 9[/TD]
[TD]A-9[/TD]
[TD]S-4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] H[/TD]
[TD][/TD]
[TD]S-9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Welcome to the MrExcel board!

Try this, copied across and down.


Book1
BCDEFGHIJKLMNOPQ
9ASSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
101813A - 9A-9S-4HS-9
11016S-10S - 6
Sum Right
Cell Formulas
RangeFormula
B10=SUMPRODUCT(--(LEFT($D10:$Q10,1)=B$9),--MID(SUBSTITUTE($D10:$Q10&"-0","-",REPT(" ",10)),10,10))
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
.. 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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
@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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,179
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top