Formula to add values from different columns that don't align

**elizo** · 05-16-2013, 09:19 AM

Hi there,

I am trying to figure out how to add values from different columns on the same worksheet, when the rows don't align.

I want to add the quarterly revenues for each company to get an annual total, but some companies have closed or opened so the rows are misaligned throughout each quarter.

Is there a lookup formula that I could use so that I would be able to add the revenue for each company's quarter without having to manual select the cells in a SUM function?

I hope someone can help me out!!

Thanks so much!

**Tony Valko** · 05-16-2013, 09:28 AM

Can you post a SMALL sample file so we can see how your data is structured? Let us know what result you expect.

**dilipandey** · 05-16-2013, 09:54 AM

Hi elizo,

welcome to the forum.
Yes, you can use some lookup formula here... and would be better if you could upload a sample workbook along with your expected output. thanks.

Regards,
DILIPandey
<click on below * if this helps>

**elizo** · 05-16-2013, 10:59 AM

Here is a sample of the excel worksheet.

I am hoping to generate a total revenue sum by looking up the matching company names in Q1, Q2, and Q3.
The company names are represented by letter values and are misaligned. Hope this helps!

And thanks so much for your replies!

DaveDeV · 05-16-2013, 11:53 AM

Here's one approach...

Your three Q1/2/3 tables re-arranged in a contiguous vertical stack, an extra column added to show the Q1/2/3 value per row and then converted to an Excel Data Table. This Data Table then used to generate a pivot table to group by company.

Attachment:
Sample of Excel Merge PLUS PIVOT.xlsx

To arrange pivot by Qtr then Company, just switch row-heading fields around as follows:
Attachment2:
Sample of Excel Merge PLUS PIVOT by Qtr.xlsx

Best Regards,

Dave

**Tony Valko** · 05-16-2013, 05:47 PM

Try this...

For company E:

=SUMIF(A5:K17,"E",E5:O17)

Note how the range references are offset.

Formula to add values from different columns that don't align

LinkBack

Thread Tools

Rate This Thread

Display

Formula to add values from different columns that don't align

Re: Formula to add values from different columns that don't align

Re: Formula to add values from different columns that don't align

Re: Formula to add values from different columns that don't align

Re: Formula to add values from different columns that don't align

Re: Formula to add values from different columns that don't align

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions