ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Auto Filter Subtotals (https://www.excelbanter.com/excel-worksheet-functions/61129-auto-filter-subtotals.html)

bob

Auto Filter Subtotals
 
I have five columns of data. Columns 1 and 2 are populated with names,
columns 3 through 5 with numbers. I would like to automatically create
subtotals in column in columns 3 through 5 for each combination of names that
appear on the same row in columns A and B.

Example:

Allen Barringer 4 16 30
Boldin Howard 6 8 4
Sievers Marks 7 10 54
Allen Marks 4 55 12
Howard Billens 44 5 66
Boldin Marks 33 55 1
Sievers Marks 5 12 40
Sievers Marks 0 22 33
Howard Billens 1 18 22

I would like to subtotal columns 3-5 each time Sievers and Marks appear on
the same row in columns A and B, respectively.

Thanks for your help,
Bob

Dave Peterson

Auto Filter Subtotals
 
How about putting another column in your data:

=a2&","b2"
and drag down.

Then sort by this column and apply Data|subtotals using that column as the key.

bob wrote:

I have five columns of data. Columns 1 and 2 are populated with names,
columns 3 through 5 with numbers. I would like to automatically create
subtotals in column in columns 3 through 5 for each combination of names that
appear on the same row in columns A and B.

Example:

Allen Barringer 4 16 30
Boldin Howard 6 8 4
Sievers Marks 7 10 54
Allen Marks 4 55 12
Howard Billens 44 5 66
Boldin Marks 33 55 1
Sievers Marks 5 12 40
Sievers Marks 0 22 33
Howard Billens 1 18 22

I would like to subtotal columns 3-5 each time Sievers and Marks appear on
the same row in columns A and B, respectively.

Thanks for your help,
Bob


--

Dave Peterson

Ashish Mathur

Auto Filter Subtotals
 
Hi,

Try the following array formula (Ctrl+shift+Enter)

=sum(if((rangeA="Sievers")*(rangeB="Marks"),rangeC ))

You may copy this range to column D and E now.

If you have any further questions, please feel free to contact me at


Regards,

Ashish

"bob" wrote:

I have five columns of data. Columns 1 and 2 are populated with names,
columns 3 through 5 with numbers. I would like to automatically create
subtotals in column in columns 3 through 5 for each combination of names that
appear on the same row in columns A and B.

Example:

Allen Barringer 4 16 30
Boldin Howard 6 8 4
Sievers Marks 7 10 54
Allen Marks 4 55 12
Howard Billens 44 5 66
Boldin Marks 33 55 1
Sievers Marks 5 12 40
Sievers Marks 0 22 33
Howard Billens 1 18 22

I would like to subtotal columns 3-5 each time Sievers and Marks appear on
the same row in columns A and B, respectively.

Thanks for your help,
Bob



All times are GMT +1. The time now is 12:38 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com