Subtotalling two columns

  • Thread starter Thread starter Harry
  • Start date Start date
H

Harry

I have three columns that list [Artist], [Title] and
[Hits]. Using the subtotal function, I can gain the total
hits of one artist. E.g.

James Star 8
James Star 10
JAMES TOTAL 18

How do I gain the total for an Artist AND a Title, so that

James Star 8
James Star 10
JAMES STAR TOTAL 18
James Sit 4
JAMES SIT TOTAL 4


At the moment I only get a total of the artist, ignoring
the differences in title! E.g.

James Star 8
James Star 10
James Sit 4
JAMES TOTAL 22


Thanks in advance.
 
Unfortunately I have thousands of records so cannot input
manually. Is there an option that might allow this in the
data/subtotals function that I've been using?
-----Original Message-----
try
=sumproduct((rngA="James")*(rngB="Star")*rngC)

I have three columns that list [Artist], [Title] and
[Hits]. Using the subtotal function, I can gain the total
hits of one artist. E.g.

James Star 8
James Star 10
JAMES TOTAL 18

How do I gain the total for an Artist AND a Title, so that

James Star 8
James Star 10
JAMES STAR TOTAL 18
James Sit 4
JAMES SIT TOTAL 4


At the moment I only get a total of the artist, ignoring
the differences in title! E.g.

James Star 8
James Star 10
James Sit 4
JAMES TOTAL 22


Thanks in advance.
Click to expand...


.
Click to expand...
 
Apply the subtotal twice, - second time, uncheck the replace subtotals.

You have to experiment to see which column to use to apply first. I believe
it would be Title first to get

James Star 8
James Star 10
JAMES STAR TOTAL 18
James Sit 4
JAMES SIT TOTAL 4
James TOTAL 22

--
Regards,
Tom Ogilvy


Harry said:
Unfortunately I have thousands of records so cannot input
manually. Is there an option that might allow this in the
data/subtotals function that I've been using?
-----Original Message-----
try
=sumproduct((rngA="James")*(rngB="Star")*rngC)

I have three columns that list [Artist], [Title] and
[Hits]. Using the subtotal function, I can gain the total
hits of one artist. E.g.

James Star 8
James Star 10
JAMES TOTAL 18

How do I gain the total for an Artist AND a Title, so that

James Star 8
James Star 10
JAMES STAR TOTAL 18
James Sit 4
JAMES SIT TOTAL 4


At the moment I only get a total of the artist, ignoring
the differences in title! E.g.

James Star 8
James Star 10
James Sit 4
JAMES TOTAL 22


Thanks in advance.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top