Summing Merged Columns

[bahinf]

Hello,

I have a 3.16MB Excel File that I use to track our Indirect Procurement Savings from a report that I download from my employer's project database. The report is in excel format and I copy and paste the data into my tracking sheet which then populates 15 individual sheets (categories), and 1 summary sheet. There has been new data that has been added to the report that I now need to incorporate into my tacking sheet and I am having difficulties figuring out the correct formula.

There are 38 columns in total, but the three columns below contain the data that I am having problems with.

One column denotes the project status (Merged Column P&Q):
- Active
- Planned
- On-Hold
- Completed

One column denotes the savings amount for each project (Column Z)

One column denotes the spend category (Merged Column AS&AT):
- Indirect Material & Services
- Indirect-CapEx
- Indirect-Cost Avoidance
- Indirect-Cost Savings

(All of this data is contain in A283:AT283 to A783:AT783)

What I need help with is how to I write the following four formulas?:
- Completed (P283:Q783), total sum of the savings amount (Z283:Z783), Indirect Material & Services (AS283:AT783)
- Completed (P283:Q783), total sum of the savings amount (Z283:Z783), Indirect-CapEx (AS283:AT783)
- Completed (P283:Q783), total sum of the savings amount (Z283:Z783), Indirect-Cost Avoidance (AS283:AT783)
- Completed (P283:Q783), total sum of the savings amount (Z283:Z783), Indirect-Cost Savings (AS283:AT783)

I would appreciate any guidance that can be provided.

Thank you in advance,

Brian

[Pete_UK]

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new I have done it for you this time.)

[bahinf]

Hi Pete -

Thank you for the response. I have cleansed the data to remove any confidential information and attached a copy per your direction.

I have highlighted the fields that I am trying to use in RED.


D15:P18 is the section that I want the results to populate in.

[bahinf]

And I should add that for now, I am only looking for Column Z (Z283:Z783) sum in the cells for Jan. (D15:D18) Future I would need Column AA283:AA783 in E15:E18, AB283:AB783 in D15:D18, etc.

[Pete_UK]

First of all, you need to change a couple of your labels in column C, as they don't match exactly with what you have in columns AS:AT, i.e. in C15 change it to "Indirect Material & Services" (not Materials), and in C18 change it to "Indirect-Cost Savings" (remove the (P&L) from the end). Then you can use this formula in D15:

=SUMIFS(Z$283:Z$783,$AS$283:$AS$783,$C15,$P$283:$P$783,"Completed")

and this can be copied down to D18. Notice that although you have merged columns, all the data will be in the left-most column, so the formula only refers to column AS and column P. Note also, that I have made the ranges cover up to row 783, as in your first post.

When you are ready, you can copy the 4 formulae across into the other monthly columns (E onwards).

Hope this helps.

Pete

[bahinf]

Hi Pete -

Thank you so very much for the help. I made the changes that you said and it works perfectly! Thank you again.

[Pete_UK]

That's good to hear.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete