Reply to: Subtotal without subtotalling?

[lilelvis2000]

Is that him standing behind you?

Luckily for me, I work from home!

[BoredBloke]

Pivot it or if you don't like pivots then do this. Get all the data into a consistent format and use SUMIFS (not Sumif). That way you can use multiple criteria. Then take your ID column and copy it to another sheet. Use remove duplicates to generate a single list of unique values. Use SumIfS to summarise against this.

I could look at it if you want

[LisaContractorUmbrella]

Kinda like my client who just loves to f**k things up. God bless him. For without him I would not earn the piddly sums that I do.

Is that him standing behind you?

[LisaContractorUmbrella]

I was going to say manager and owner but then remembered that its part of your brother's SJD group.

Did you get your company horse in the end?

Yep Simon's the big cheese - I'm the cheese niblet

No I bloody didn't - I'll have a word with him about that!

[lilelvis2000]

Actually I'm the Managing Director I also love spreadsheets which is why I still get involved with this stuff (probably much to the annoyance of the team who'd far rather I was off playing golf or something )

Kinda like my client who just loves to f**k things up. God bless him. For without him I would not earn the piddly sums that I do.

[eek]

Actually I'm the Managing Director I also love spreadsheets which is why I still get involved with this stuff (probably much to the annoyance of the team who'd far rather I was off playing golf or something )

I was going to say manager and owner but then remembered that its part of your brother's SJD group.

Did you get your company horse in the end?

[LisaContractorUmbrella]

Lisa is the manager...

Actually I'm the Managing Director I also love spreadsheets which is why I still get involved with this stuff (probably much to the annoyance of the team who'd far rather I was off playing golf or something )

[eek]

I see there is a decent solution to this. But anyway. Yes there are various ways to determine the last row of data so the loop would not be fixed.
You seem to be doing a lot of complex Excel work...might think about dropping a VBA training course leaflet to the manager. I think it would be worth it.

Lisa is the manager...

[LisaContractorUmbrella]

I see there is a decent solution to this. But anyway. Yes there are various ways to determine the last row of data so the loop would not be fixed.
You seem to be doing a lot of complex Excel work...might think about dropping a VBA training course leaflet to the manager. I think it would be worth it.

Ok thanks for that - yes, you're right a VBA training course would probably be a good idea

[lilelvis2000]

Not familiar with loops at all Quite happy to record macro's but not really any good at writing them! Can kind of understand what you've written - will this still work if the number of rows vary? I can't give a definitive end point

I see there is a decent solution to this. But anyway. Yes there are various ways to determine the last row of data so the loop would not be fixed.
You seem to be doing a lot of complex Excel work...might think about dropping a VBA training course leaflet to the manager. I think it would be worth it.

[LisaContractorUmbrella]

OK I am experimenting with data, I see that you need to have totals for each employee for each month as well as totals for the each employee for all months - right?

So the first one you have in column D already and you want the second in column E.

How important is it to have those values in those columns, can there be somewhere else? What is it you need to do with this data afterwards?

Yep you've got it. The data can't really be anywhere else as I need to be able to view names and reference numbers which are attached to the data. Once this is resolved I need to vlookup corresponding data on another worksheet and then compare the two figures to give a variance i.e. the difference in totals for each reference number on the two worksheets.

[darrylmg]

Have tried a couple of times and get the same result - when there was less data it would take around 45 minutes to enter the subtotals so am pretty sure it's just shear volume combined with nested Vlookup functions which I understand take up a lot of processing power

But have you tried switching it off and back on again...

[MPwannadecentincome]

OK I am experimenting with data, I see that you need to have totals for each employee for each month as well as totals for the each employee for all months - right?

So the first one you have in column D already and you want the second in column E.

How important is it to have those values in those columns, can there be somewhere else? What is it you need to do with this data afterwards?

[LisaContractorUmbrella]

Thanks Mudskipper you're a great help

[mudskipper]

A Employee ID B Month C Amount D Total for Month (Calc: =IF(AND(A2=A1,B2=B1),0,C2)

1235 1 25.00
1235 1 15.00 40.00
1235 2 25.00 25.00
1235 3 125.00 125.00
2569 2 25.00
2569 2 15.00 40.00
2569 3 300.00
2870 3 25.00 325.00

What I need is a calculation in column E which will sum Column D at each change of reference in column A

I spent ages lining up all those numbers which now look fine whilst I am editing but all group together when I post

There you go

A Employee ID             B Month        C Amount          D 
Total for Month (Calc: =IF(AND(A2=A1,B2=B1),0,C2)

1235                              1                   25.00 
1235                              1                   15.00            40.00
1235                              2                   25.00            25.00
1235                              3                 125.00           125.00
2569                              2                   25.00
2569                              2                   15.00            40.00 
2569                              3                 300.00 
2870                              3                   25.00           325.00