Doing a subtotal for a non defined range of cells

[sproulx]

I'm trying to do a subtotal for a division that contain numbers and I may have 50 or more divisions with no predifine number of data. Can you please help me find a formula that will do a subtotal at each divison when it finds one.
this month:
PPCA
200
300
400
PPCD
500
600
PPSE
800
900
200
100

next month:
PPCA
200
PPCD
500
600
700
PPSE
200
300
PPSD
150

So the number of cell that should be in the range varies and the number of division may vary as well.

Thank you,

[BB1972]

sproulx,

Posting an example file will generally help you get better results/responses. However, for now - are all the entries above (ppca, 200, 300, etc) intended to go in the same column?

[sproulx]

Yes! When you open the file where you see PPCM I want to subtotal everything underneath it and do that for each division in the report.

[BB1972]

Here you go

I had to be a little sneaky, and use column F as a helper column (which I then hid) to place the "maths" ie running subtotals in. A little bit of conditional formatting to hide "FALSE" where there are 2 consecutive falses, and you're sorted. I'm sure there are much better ways to get the result you wanted, but.... it's the result you wanted.

[sproulx]

Thank you very much for your help!!!

[BB1972]

You've very welcome - glad I could help. Will you please be sure to mark this thread "solved" if you're happy that it is, so others don't spend time reading about a resolved issue. Thank you.

[sproulx]

Is there a way to make the total appear next to the division so I could refer to it using vlookup. I would like to see the total next to PPCM for example is that possible?

Thanks again,

[BB1972]

Do you mean like this:

[sproulx]

Actually no, it has to be next to the upper value where it find text

the 2 799 028 should be next to PPCM at the top as it is the subtotal for that division.

Thanks,

[Ron Coderre]

Using your posted workbook...I *think* this regular formula does what you want

Please Login or Register  to view this content.

Copy that formula down through F927.
Note: for the last total to work E928 must contain text with at least 5 characters.

Is that something you can work with?

[sproulx]

It workssss!!!!! I'm so happy it makes my day! It is nice to know there are very intelligent people out there wanting to share there knowledge.

Thank you!

[BB1972]

Using your posted workbook...I *think* this regular formula does what you want

Please Login or Register  to view this content.

Copy that formula down through F927.
Note: for the last total to work E928 must contain text with at least 5 characters.

Is that something you can work with?

Ron,

That's great - much better than my ham-fisted attempt If you get a chance, could you tell me the significance of * as the MATCH lookup value?

Thanks,

[BB1972]

It workssss!!!!! I'm so happy it makes my day! It is nice to know there are very intelligent people out there wanting to share there knowledge.

Thank you!

Glad you got sorted, sproulx.

[Ron Coderre]

Ron,

That's great - much better than my ham-fisted attempt If you get a chance, could you tell me the significance of * as the MATCH lookup value?

Thanks,

The asterisk in the MATCH function causes it to match on any text...ignoring numbers.

[Ron Coderre]

It workssss!!!!! I'm so happy it makes my day! It is nice to know there are very intelligent people out there wanting to share there knowledge.

Thank you!

Thanks for the kind words....I'm glad I could help.

[BB1972]

The asterisk in the MATCH function causes it to match on any text...ignoring numbers.

Many thanks, Ron - that'll help me, going forward.

[Cutter]

@ sproulx

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.