SUM two columns with the same heading.

[smirlface]

Hi all,

I am trying to find a formula that will sum the values of two columns with the same heading (in my case the heading is "GST"). Every month this info changes, so the GST may not be in the same column all the time. I have around 1000 rows I need to sum. Example data below:

Amount	GST	Amount Total	Card Fee	GST	Card Fee Total
100	10	110	50	5	55

Is there a formula when I can sum two columns based on the column NAME?

Thanks,
Audrey

[alansidman]

Because you did not identify the version of Excel you are using, I will give you the answer if you are using 2007 or later.

Convert your range to a Table. Name your Table as Table1. In column G, row2, type = Table1[GST] + Table1[GST2]

Excel will automatically rename the second GST field name to GST2, because you cannot have a table with two fields named the same. Also because you have created a table, it will automatically fill down all the cells in Column G with the formula.

Hope this clarifies your issue.

Also for further reference, please update your profile to reflect the version of excel you are using so that the appropriate solution may be provided.

[smirlface]

Because you did not identify the version of Excel you are using, I will give you the answer if you are using 2007 or later.

Convert your range to a Table. Name your Table as Table1. In column G, row2, type = Table1[GST] + Table1[GST2]

Excel will automatically rename the second GST field name to GST2, because you cannot have a table with two fields named the same. Also because you have created a table, it will automatically fill down all the cells in Column G with the formula.

Hope this clarifies your issue.

Also for further reference, please update your profile to reflect the version of excel you are using so that the appropriate solution may be provided.

Thanks for getting back to me. I use Microsoft Excel 2010 (also updated my profile to reflect this). The person that will be using this spreadsheet has limited skills with Excel so I was wondering if there was a formula that will be able to find the columns and sum them so all she had to do was dump the data that she gets into a spreadsheet.

Thanks again,
Audrey

[sktneer]

You may try this.....

Please Login or Register  to view this content.

In the formula A1:F1 contains the column headers and A2:F2 contains data. You may change A2:F2 to as per your actual range of data (like A2:F5000).

[smirlface]

You may try this.....

Please Login or Register  to view this content.

In the formula A1:F1 contains the column headers and A2:F2 contains data. You may change A2:F2 to as per your actual range of data (like A2:F5000).

Thanks! that works, just to be extra picky is there a formula that can work if there's text in the data? Eg. Below.

Type	Amount	GST	Total Amount	Type	Card Fee	GST	Total Card Fee
Fuel	100	10	110	Card 57	50	5	55

Thanks,

[sktneer]

If the sum range may contain text entries also, you may try this.....

Please Login or Register  to view this content.

[smirlface]

If the sum range may contain text entries also, you may try this.....

Please Login or Register  to view this content.

Hi, looks like this is doing a count of the data, not a sum. is there a way to sum?

[Saarang84]

Hi, looks like this is doing a count of the data, not a sum. is there a way to sum?

Try,

Please Login or Register  to view this content.

where A1:H1 is the header row, A2:H2 is the row containing the data you want to sum.

[Jacc]

Trying to put it all together, it's always good to have workbook at hand when trying out solutions (thanks, sktneer!)