Adding text to the subtotal row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this sample data:

Project # City State Address Department Staff
1 Phoenix AZ 123 Fake Street HR 3
1 Phoenix AZ 123 Fake Street Legal 2
2 Phoenix AZ 123 Fake Street IT 7
3 Phoenix AZ 999 Square Ave. HR 2
3 Phoenix AZ 999 Square Ave. IT 1
3 Phoenix AZ 999 Square Ave. Sales 4

I need to subtotal by Project and sum all staff, but I need to have the
Address show up on the subtotal row. Is there an easier way of doing this
than copy/paste, considering my actual data includes ~500 projects, and some
addresses have multiple projects (so I can't subtotal by address)?

Thanks, officewrkr
 
I have this sample data:

Project # City State Address Department Staff
1 Phoenix AZ 123 Fake Street HR 3
1 Phoenix AZ 123 Fake Street Legal 2
2 Phoenix AZ 123 Fake Street IT 7
3 Phoenix AZ 999 Square Ave. HR 2
3 Phoenix AZ 999 Square Ave. IT 1
3 Phoenix AZ 999 Square Ave. Sales 4

I need to subtotal by Project and sum all staff, but I need to have the
Address show up on the subtotal row. Is there an easier way of doing this
than copy/paste, considering my actual data includes ~500 projects, and some
addresses have multiple projects (so I can't subtotal by address)?

Thanks, officewrkr
Click to expand...

after you do the subtotal from the subtotal function add the address
as follows:
=SUBTOTAL(9,f14:f15)&" "&d14

the final should look like this

A B C D E F
Project # City State Address Department Staff
1 Phoenix AZ 123 Fake Street HR 3
1 Phoenix AZ 124 Fake Street Legal 2
1 Total 5 123 Fake Street
2 Phoenix AZ 125 Fake Street HR 7
2 Total 7 125 Fake Street
3 Phoenix AZ 126 Fake Street Legal 2
3 Phoenix AZ 127 Fake Street HR 1
3 Phoenix AZ 128 Fake Street Legal 4
3 Total 7 128 Fake Street
Grand Total 19
6
 
Titus said:
after you do the subtotal from the subtotal function add the address
as follows:
=SUBTOTAL(9,f14:f15)&" "&d14

the final should look like this

A B C D E F
Project # City State Address Department Staff
1 Phoenix AZ 123 Fake Street HR 3
1 Phoenix AZ 124 Fake Street Legal 2
1 Total 5 123 Fake Street
2 Phoenix AZ 125 Fake Street HR 7
2 Total 7 125 Fake Street
3 Phoenix AZ 126 Fake Street Legal 2
3 Phoenix AZ 127 Fake Street HR 1
3 Phoenix AZ 128 Fake Street Legal 4
3 Total 7 128 Fake Street
Grand Total 19
6


Thanks Titus, I did that and it worked, but it seems that I would have to do that for each Total row which would be as cumbersome as copy/paste. Ideally, I would be able to subtotal projects and sum column F while setting column D on the total row equal to the address listed in column D.
Click to expand...
 
Back
Top