Sumif Macro

[bhall]

Okay, I know this is kind of hard to see on the below table, but I couldnt get the excel file to post. I am looking for a macro that will run a sumif on rows where the 'CA" (column A) is blank for each resource name. for example, I want a sumif value to appear under july 15 (currently shows 75.42) for everything with a resource assignment of "PM"; if you scroll down a bit, you will see that the "PM" will change to "PE" ( and column A will again be blank) around row 11, at which point I want to see a sum in row 11 for everything with a resource of "PE". Additionally, I need this macro to do this for each month on the ws.

The reason I need this is because the data for the months is simply a pull (paste values) from a resource loaded project file. as task estimates are updated by each functional dept, I want to see the sum for each resource category roll-up appropriately. I already have a macro that automatically removes unnecessary text (i.e., the "h" that occurs when data is pulled from ms project), but I am way beyond my skill level with the macro I am requesting. Any help is greatly appreciated.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 829"]
<colgroup><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD]CA[/TD]
[TD]CA Description[/TD]
[TD]Resource[/TD]
[TD]WBS[/TD]
[TD]Resource Name[/TD]
[TD="align: right"]Jul-15[/TD]
[TD="align: right"]Aug-15[/TD]
[TD="align: right"]Sep-15[/TD]
[TD="align: right"]Oct-15[/TD]
[TD="align: right"]Nov-15[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]PM[/TD]
[TD] [/TD]
[TD]PM[/TD]
[TD="align: right"]75.42[/TD]
[TD="align: right"]68.85[/TD]
[TD="align: right"]72.13[/TD]
[TD="align: right"]108.2[/TD]
[TD="align: right"]103.28[/TD]
[/TR]
[TR]
[TD]1.1.1[/TD]
[TD]Program Management[/TD]
[TD]PM[/TD]
[TD]1.1.1.1.1[/TD]
[TD] PM NRE Phase[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]72.13[/TD]
[TD="align: right"]68.85[/TD]
[/TR]
[TR]
[TD]1.1.1[/TD]
[TD]Program Management[/TD]
[TD]PM[/TD]
[TD]1.1.1.1.2[/TD]
[TD] PM Production Phase[/TD]
[TD="align: right"]37.7[/TD]
[TD="align: right"]34.43[/TD]
[TD="align: right"]36.07[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.1.1[/TD]
[TD]Program Management[/TD]
[TD]PM[/TD]
[TD]1.1.1.2.1[/TD]
[TD] PA NRE Phase[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]36.07[/TD]
[TD="align: right"]34.43[/TD]
[/TR]
[TR]
[TD]1.1.1[/TD]
[TD]Program Management[/TD]
[TD]PM[/TD]
[TD]1.1.1.2.2[/TD]
[TD] PA Production Phase[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.1.3[/TD]
[TD]Reviews and Audits[/TD]
[TD]PM[/TD]
[TD]1.1.3.1[/TD]
[TD] Review 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.1.3[/TD]
[TD]Reviews and Audits[/TD]
[TD]PM[/TD]
[TD]1.1.3.2[/TD]
[TD] Review 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.1.3[/TD]
[TD]Reviews and Audits[/TD]
[TD]PM[/TD]
[TD]1.1.3.3[/TD]
[TD] Review 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.1.3[/TD]
[TD]Reviews and Audits[/TD]
[TD]PM[/TD]
[TD]1.1.3.4[/TD]
[TD] Review 4[/TD]
[TD="align: right"]37.7[/TD]
[TD="align: right"]34.43[/TD]
[TD="align: right"]36.07[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 829"]
<colgroup><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD]PE[/TD]
[TD] [/TD]
[TD]PE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]43.28[/TD]
[TD="align: right"]41.32[/TD]
[/TR]
[TR]
[TD]1.1.2[/TD]
[TD]Project Engineering[/TD]
[TD]PE[/TD]
[TD]1.1.2.1.1[/TD]
[TD] PE NRE Phase[/TD]
[TD="align: right"]7.53[/TD]
[TD="align: right"]6.88[/TD]
[TD="align: right"]7.22[/TD]
[TD="align: right"]36.07[/TD]
[TD="align: right"]34.43[/TD]
[/TR]
[TR]
[TD]1.1.2[/TD]
[TD]Project Engineering[/TD]
[TD]PE[/TD]
[TD]1.1.2.1.2[/TD]
[TD] PE Production Phase[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.1.2[/TD]
[TD]Project Engineering[/TD]
[TD]PE[/TD]
[TD]1.1.2.2.1[/TD]
[TD] QA NRE Phase[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.22[/TD]
[TD="align: right"]6.88[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Mackers]

Hi bhall

Have you considered using pivot tables for this? It seems like they would be a perfect fit. Highlight your data, click Insert - Pivot Table, drag the Resource into the bottom left and the sum columns into the Values.

If not, then you can use a combination of the formula to pull a list of uniques from the Resource column, and then sum using a standard SUMIF() formula. If you search the forum for 'list of uniques' you should find it.

Good luck

Mackers

 

[bhall]

Hi bhall

Have you considered using pivot tables for this? It seems like they would be a perfect fit. Highlight your data, click Insert - Pivot Table, drag the Resource into the bottom left and the sum columns into the Values.

If not, then you can use a combination of the formula to pull a list of uniques from the Resource column, and then sum using a standard SUMIF() formula. If you search the forum for 'list of uniques' you should find it.

Good luck

Mackers

Thanks for the quick reply. I was basically trying to avoid adding another tab to the workbook, yet still be able to "paste values" from the resource-loaded project file. I know that I can write formulas within the spreadsheet to do what I'm asking, but I was trying to put it into a macro, because once I paste values from ms project, any formulas within the worksheet would be lost. The issue is that the end users of this form will be functional managers, and I do not want/trust them to enter any type of summation formulas or pivot tables into the workbook. I was hoping that there was some way that I could just have them simply update their hours (by month and task) and everything else would take care of itself. Not sure it matters, but this data is automatically extracted from the current worksheet and applied to various reports.