Listing the values that were added by COUNTIF/SUMIF or gettign the summary of the values (

L

leoini

New Member
Joined
Jul 6, 2017
Messages
11
Hello,

I have an excel with a lot of data (attached excel is just a small portion of it).
In this excel, my primary objective is to find the EFFORT by each RESOURCE to each PROJECT.
If i use SUMIF, i get the total effort for each resource easily.
However, I want to know list the PROJECTS and respective EFFORT for each RESOURCE (if effort for a particular PROJECT has zero EFFORT by an associate, then that project need not be listed)

Below is the sample of the data I have. Full sheet has too many values like this (one more reason why i don't want the PROJECT that zero EFFORT by an associate need not be listed)

For some reason, i am unable to attache the file. Thanks in advance for your time.

[TABLE="width: 455"]
<tbody>[TR]
[TD]Task[/TD]
[TD]Project[/TD]
[TD]Priority[/TD]
[TD]Resource[/TD]
[TD]Status[/TD]
[TD]Effort[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01234[/TD]
[TD]Medium[/TD]
[TD]Ramesh[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01235[/TD]
[TD]Low[/TD]
[TD]Krish[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01236[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]Completed[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01237[/TD]
[TD]High[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01238[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01239[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01240[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01250[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01251[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]Completed[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01252[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]In Progress[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01253[/TD]
[TD]Low[/TD]
[TD]Krish[/TD]
[TD]In Progress[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01239[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]On Hold[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01240[/TD]
[TD]High[/TD]
[TD]Mani[/TD]
[TD]Completed[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01247[/TD]
[TD]High[/TD]
[TD]Mor[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01248[/TD]
[TD]Medium[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01249[/TD]
[TD]Medium[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01250[/TD]
[TD]High[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01251[/TD]
[TD]Medium[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01252[/TD]
[TD]High[/TD]
[TD]Mor[/TD]
[TD]Completed[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01253[/TD]
[TD]Low[/TD]
[TD]Krish[/TD]
[TD]In Progress[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01254[/TD]
[TD]Medium[/TD]
[TD]Krish[/TD]
[TD]On Hold[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01255[/TD]
[TD]Medium[/TD]
[TD]Ramesh[/TD]
[TD]Completed[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01256[/TD]
[TD]High[/TD]
[TD]Mor[/TD]
[TD]In Progress[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01257[/TD]
[TD]Medium[/TD]
[TD]Mani[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01237[/TD]
[TD]Medium[/TD]
[TD]Ramesh[/TD]
[TD]Completed[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01238[/TD]
[TD]Medium[/TD]
[TD]Mor[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01239[/TD]
[TD]High[/TD]
[TD]Bari[/TD]
[TD]Completed[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01250[/TD]
[TD]Medium[/TD]
[TD]Bari[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01251[/TD]
[TD]Medium[/TD]
[TD]Bari[/TD]
[TD]Completed[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01252[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01253[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report[/TD]
[TD]P-01265[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Design[/TD]
[TD]P-01265[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]CAD[/TD]
[TD]P-01265[/TD]
[TD]Medium[/TD]
[TD]Suba[/TD]
[TD]In Progress[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
If you want to use a macro, only change in these lines for your data

Code: 
    Set s1 = Sheets("Sheet1")   'sheet name with data
    Set s2 = Sheets("Sheet2")   'sheet name with results
    '
    cp = "B"    'Project column
    cr = "D"    'Resource column
    ce = "F"    'Effort column



I did a test with 25000 records in a time of 7 seconds.
Note: The result will be in sheet2 in columns D to F

Code: 
Sub Listing_Values()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim cp As String, cr As String, ce As String, resource As String, project As String
    Dim u1 As Long, u2 As Long, u3 As Long, i As Long, j As Long, k As Long, sumEffor As Long


    Application.ScreenUpdating = False
    Application.StatusBar = False
    Application.Calculation = xlCalculationManual
    
    Set s1 = Sheets("Sheet1")   'sheet name with data
    Set s2 = Sheets("Sheet2")   'sheet name with results
    '
    cp = "B"    'Project column
    cr = "D"    'Resource column
    ce = "F"    'Effort column
    '
    s2.Cells.ClearContents
    s2.Range("D1:F1").Value = Array("Resource", "Project", "Effort")
    If s1.AutoFilterMode = True Then s1.AutoFilterMode = False
    u1 = s1.Range(cr & Rows.Count).End(xlUp).Row
    s1.Range(cr & "1:" & cr & u1).Copy s2.Range("A1")
    u2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    s2.Range("A1:A" & u2).RemoveDuplicates Columns:=1, Header:=xlYes
    '
    k = 2
    u3 = s2.Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To u3
        resource = s2.Cells(i, "A").Value
        Application.StatusBar = "Processing resource : " & resource & " : " & i - 1 & " of : " & u3 - 1
        If s1.AutoFilterMode = True Then s1.AutoFilterMode = False
        u1 = s1.Range(cr & Rows.Count).End(xlUp).Row
        s1.Range("A1:F" & u1).AutoFilter Field:=Columns(cr).Column, Criteria1:=resource
        s2.Columns("B").ClearContents
        u1 = s1.Range(cr & Rows.Count).End(xlUp).Row
        s1.Range(cp & "1:" & cp & u1).Copy s2.Range("B1")
        u2 = s2.Range("B" & Rows.Count).End(xlUp).Row
        s2.Range("B1:B" & u2).RemoveDuplicates Columns:=1, Header:=xlYes
        u2 = s2.Range("B" & Rows.Count).End(xlUp).Row
        For j = 2 To u2
            project = s2.Cells(j, "B").Value
            sumEffor = WorksheetFunction.SumIfs( _
                        s1.Range(ce & "2:" & ce & u1), _
                        s1.Range(cr & "2:" & cr & u1), resource, _
                        s1.Range(cp & "2:" & cp & u1), project)
            If sumEffor > 0 Then
                s2.Cells(k, "D").Value = resource
                s2.Cells(k, "E").Value = project
                s2.Cells(k, "F").Value = sumEffor
                k = k + 1
            End If
        Next
    Next
    If s1.AutoFilterMode = True Then s1.AutoFilterMode = False
    
    Application.ScreenUpdating = True
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    
    MsgBox "End"
End Sub
 
Upvote 0
Hi xjohnson,

I tried what you provided but it did not work the way i wanted.
It seems to get the first instance of a project related to the resource & the corresponding effort. How ever i want the cumulative.
If i use COUNTIFS, i get the total number of columns for a RESOURCE that have efforts in them (i get 6 for Suba). If I use SUMIF, i get the total effort for the RESOURCE too.
However, i want to know the project numbers in these 6 rows and then check if some of these projects are same (just like the last 3 rows in the excel that i have attached). If not, then i want to list all these projects and the efforts against each of these projects.
If the project numbers repeat, then i want to show that project only once & all the effort against that also once. That is what i have done with the dummy result for Suba.
Thanks
 
Upvote 0
I was able to get the result i want with the below formulas (I had to populate some cells H1=text "Resource", I1=text "Project",J1=text "Effort"

I2 = To get the project for a particular resource (it filters out 0 effort projects) = IFERROR(INDEX($B$5:$B$999,MATCH(1,INDEX(($D$5:$D$999=$H$2)*($F$5:$F$999<>"")/NOT(COUNTIF(I$1:I1,$B$5:$B$999)),),)),"")

J2 = To get the effort of each of the projects that is listed be the above formula =SUMIFS(F:F,B:B,I2,D:D,H$2)

I did not try the macro since I am not familiar with it. Thanks for the help.
 
Upvote 0
leoini said:
I did not try the macro since I am not familiar with it. Thanks for the help.
Click to expand...

Do not worry, but if you want to try it.

Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. On the sheet that opens, paste this code:

Code: 
Sub Listing_Values()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim cp As String, cr As String, ce As String, resource As String, project As String
    Dim u1 As Long, u2 As Long, u3 As Long, i As Long, j As Long, k As Long, sumEffor As Long




    Application.ScreenUpdating = False
    Application.StatusBar = False
    Application.Calculation = xlCalculationManual
    
    Set s1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")   'sheet name with data
    Set s2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")   'sheet name with results
    '
    cp = "[COLOR=#ff0000]B[/COLOR]"    'Project column
    cr = "[COLOR=#ff0000]D[/COLOR]"    'Resource column
    ce = "[COLOR=#ff0000]F[/COLOR]"    'Effort column
    '
    s2.Cells.ClearContents
    s2.Range("D1:F1").Value = Array("Resource", "Project", "Effort")
    If s1.AutoFilterMode = True Then s1.AutoFilterMode = False
    u1 = s1.Range(cr & Rows.Count).End(xlUp).Row
    s1.Range(cr & "1:" & cr & u1).Copy s2.Range("A1")
    u2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    s2.Range("A1:A" & u2).RemoveDuplicates Columns:=1, Header:=xlYes
    '
    k = 2
    u3 = s2.Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To u3
        resource = s2.Cells(i, "A").Value
        Application.StatusBar = "Processing resource : " & resource & " : " & i - 1 & " of : " & u3 - 1
        If s1.AutoFilterMode = True Then s1.AutoFilterMode = False
        u1 = s1.Range(cr & Rows.Count).End(xlUp).Row
        s1.Range("A1:F" & u1).AutoFilter Field:=Columns(cr).Column, Criteria1:=resource
        s2.Columns("B").ClearContents
        u1 = s1.Range(cr & Rows.Count).End(xlUp).Row
        s1.Range(cp & "1:" & cp & u1).Copy s2.Range("B1")
        u2 = s2.Range("B" & Rows.Count).End(xlUp).Row
        s2.Range("B1:B" & u2).RemoveDuplicates Columns:=1, Header:=xlYes
        u2 = s2.Range("B" & Rows.Count).End(xlUp).Row
        For j = 2 To u2
            project = s2.Cells(j, "B").Value
            sumEffor = WorksheetFunction.SumIfs( _
                        s1.Range(ce & "2:" & ce & u1), _
                        s1.Range(cr & "2:" & cr & u1), resource, _
                        s1.Range(cp & "2:" & cp & u1), project)
            If sumEffor > 0 Then
                s2.Cells(k, "D").Value = resource
                s2.Cells(k, "E").Value = project
                s2.Cells(k, "F").Value = sumEffor
                k = k + 1
            End If
        Next
    Next
    If s1.AutoFilterMode = True Then s1.AutoFilterMode = False
    
    Application.ScreenUpdating = True
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    
    MsgBox "End"
End Sub

Change the names of the sheets (in red) to reflect your workbook AND the letter (in red) of your columns data. Close the editor (press Alt-Q). From Excel, press Alt-F8 to open the macro selector, and select Listing_Values and press Run.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top