Help: Formula to find row header, then sum that column
Hello! This is my first time posting. I'm working on a spreadsheet and here is my dilemma:
I would like to identify the row header, then sum the values in the column under that header.
Sounds simple enough, but I cannot get it. I have tried various combinations of HLOOKUP, SUM, SUMIF, and INDEX/MATCH
Any help would be appreciated!
For example, I would like to identify in the top row, which column has header 'Ad_impressions', then sum the values in that row to give me the sum
I understand how to use hlookup, but instead of returning the value in the next row, I want to sum the values in that column under the header
Try something like this...
Data Range
This formula entered in B10:
=SUM(INDEX(B2:D5,0,MATCH(A10,B1:D1,0)))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Try this:
MatchCol.xlsx
Originally Posted by cyiangou
Sorry for off-topic interjection:
Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
Thanks again for all your hard work here!
Hi Tony
Agree with you entirely. But sometimes the question is extremely clear, and when the answer (especially a small one), is a direct fulfillment of the OP, then further explanation seems redundant. For example:
Q: I would like to identify the row header, then sum the values in the column under that header.
A: Try this formula: It identifies the row header, then sums the values in the column under that header.
If I had devised some unintuitively weird way of solving it, I would have provided further explanation.
But agreed, such terse replies don't form a natural 'bullet point' within the thread. Even 'This should do what you need' would have been better than 'Try this' as it identifies itself as an answer.
Sorry, you made me think.
Thanks Tony Valko, but I am getting a value error. My data in some columns are text, and in other its numerical. The column I would like to sum is numerical of course, but I believe mixing text data in this table means that this formula will not work.
The SUM function will ignore text entries.
Can you post a SMALL sample file and show us what result you expect?
A SMALL file will have about 20 rows worth of data.
Ad impressions CTR CPC Network Name 100 3 2 GDN Client 1 75 2 4 SEM Client 2 100 3 3 SEM Client 3 80 5 4 GDN Client 4 30 2 1 SEM Client 5
I would like the formula to find the column labeled Ad_impressions, then sum the values in this row.
I need it to be able to seek out the column header itself because the data I import will not always have the columns in the same order. For ex. it may be ad_impressions/ctr/cpc/network/name the first time, and another time it may be imported as cpc/name/network/ad_impressions/ctr... so no matter what order the columns are in, it needs to be able to seek out ad_impressions and then sum the total number of impressions in that column
Data Range
This formula entered in B10:
=SUM(INDEX(A2:E6,0,MATCH(A10,A1:E1,0)))
Here's a small sample file to demonstrate this.
Nevermind, I got it! Thank you very much for your help!
Good deal. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
great thanks! this is exactly what i am looking for. however, i am getting the #Value! error, but possibly related to syntax since i am posting the formula on a separate sheet from the data table
Post the EXACT formula that you are trying to use.
Perhaps if you upload an example workbook? (Go Advanced>Manage Attachments) Sounds like your data might be all text and no numbers (even if they look like numbers)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks