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

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.

TaskProjectPriorityResourceStatusEffort
CADP-01234MediumRameshIn Progress
CADP-01235LowKrishIn Progress
CADP-01236MediumManiCompleted3
CADP-01237HighManiIn Progress3
CADP-01238MediumManiIn Progress
CADP-01239MediumManiIn Progress3
DesignP-01240MediumManiIn Progress4
CADP-01250MediumManiIn Progress
ReportP-01251MediumManiCompleted12
DesignP-01252MediumManiIn Progress34
ReportP-01253LowKrishIn Progress45
CADP-01239MediumManiOn Hold
CADP-01240HighManiCompleted34
ReportP-01247HighMorCompleted
ReportP-01248MediumMorIn Progress6
DesignP-01249MediumMorIn Progress
CADP-01250HighMorIn Progress99
CADP-01251MediumMorIn Progress1
CADP-01252HighMorCompleted2
CADP-01253LowKrishIn Progress23
CADP-01254MediumKrishOn Hold34
DesignP-01255MediumRameshCompleted45
ReportP-01256HighMorIn Progress34
DesignP-01257MediumManiCompleted
ReportP-01237MediumRameshCompleted23
CADP-01238MediumMorCompleted
CADP-01239HighBariCompleted8
CADP-01250MediumBariCompleted
CADP-01251MediumBariCompleted23
CADP-01252MediumSubaIn Progress2
DesignP-01253MediumSubaIn Progress
ReportP-01265MediumSubaIn Progress4
DesignP-01265MediumSubaIn Progress5
CADP-01265MediumSubaIn Progress4

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,759
Office Version
2007
Platform
Windows
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
 

leoini

New Member
Joined
Jul 6, 2017
Messages
11
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
 

leoini

New Member
Joined
Jul 6, 2017
Messages
11
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,759
Office Version
2007
Platform
Windows
I did not try the macro since I am not familiar with it. Thanks for the help.
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,377
Messages
5,468,256
Members
406,575
Latest member
Joe00

This Week's Hot Topics

Top