VBA to Sum last row of column based on data from Column A

[libby845]

Hello -
I have a spreadsheet that has VBA to sum the final row.
Range("B" & LastRow + 1).Formula = "=(SUM(B10:B" & LastRow & "))"

This worked perfectly until areas of responsibility subtotals were added to the report.
For example Oncology 20
250011130 - 4 East 15
250013456 - 2 North 5
Women and Children 33
250015434 - Pediatrics 18
250014020 - 3 West 5
250036578 - 3 North 10

The current macro sums everything together with a result of 106, however the result we want is 53. I thought about dividing by 2, but I can't because at times the users hide the detail and only show the Area of responsibility, so the result would be incorrect.
The number of departments below Area of responsibility changes constantly as do the total departments.
I think I need a SUMIF type command based on if it starts with a character sum it, and if it starts with a number ignore. I don't know how to write that into the macro statement.

Can anyone help?

 

[Beyond_avarice]

So I added "East", "North" and "Women and Children" =53

Are these the only departments you want to Sum? If so, then yes. You will want to utilize a SUMIF to identify the criteria for what values to SUM.

 

[libby845]

Hi - No that is just an example. In that example we need to sum Oncology = 20 and Women and Children = 33. This sheet has hundreds of departments (the ones that begin with numbers) and probably 75 areas of responsibility (begins with words). I don't have a constant word or number to use.

 

[Beyond_avarice]

So how do we take your knowledge of what is to be included and use that in a formula?

There has to be something that clues you in to what to include. If you can't come up with that; then nobody will be able to help you delineate your lists down to the desired results.

It looks like you have "Oncology" and "Women and Children" as some sort of "group".

Will these Two always be included in your list? Should we also include items like "Pathology", "Pheblotomy", "Geriatric" etc?

 

[libby845]

Can you tell I work for a hospital? Besides hospital terms and areas of responsibility always beginning with letters and not numbers, I don't know how advise as to a common grouping. Unfortunately they don't all contain a common term and the descriptions can change as managers shift responsibilities. I'm always surprised by the number of constant changes.


Is it possible to use an if statement with the left most character being a letter and not a number? If letter sum and if number ignore?

 

[Michael M]

Is this all in one cell ?
250011130 - 4 East 15

 

[libby845]

yes. It's the department number and name all in Column A. Column A also contains the "area of responsibility" Oncology, Women and Children, Radiology, Administration, etc.

 

[libby845]

The values I need to Sum down are in Column B

 

[Beyond_avarice]

So these areas of Responsibilities ALWAYS begin with letters and not numbers. Now we have something to work with.

I have to go do something else, so I'll let someone else take this on. They will likely use a "Left" function to identify if the first characters are Text or Numbers and go from there. If no one takes it on. I'll check back probably tomorrow and see if I can come with something.

 

[libby845]

Always.

Thank you for your help!