CODEWITHSUNDEEP
excelvba
jay747
jay747

Reputation: 47

excel vba - sumif and average by id field

Hi I am new to excel , was looking for a way to calculate the following:

Have id field in column A in text format. Based on each ID field and where Text field is not empty, i want to set the Pos field at value 1 (at the start of each new ID) and then increment to 2 at the next text field position which is not empty.

The id should reset to 1 when the id in column A changes and then increment and so on.

Also the Final Sc should be calculated for each Pos as simple average of Sc between 2 positions and printed at the beginning of each POS.

eg. for ID 12534, for Pos 1, Final Sc should be (0.2+0.3)/2.

enter image description here

Upvotes: 0

Views: 160

Answers (3)

DisplayName
DisplayName

Reputation: 13386

you can use SpecialCells() method and Areas property of Range object:

Sub main()
    Dim area As Range
    Dim iArea As Long

    With Range("A2", Cells(Rows.Count, 1).End(xlUp))
        For Each area In .SpecialCells(xlCellTypeConstants).Areas
            With area.Offset(, 1).SpecialCells(xlCellTypeBlanks)
                For iArea = 1 To .Areas.Count
                    .Areas(iArea).Offset(-1, 2).Resize(1).Value = iArea
                    .Areas(iArea).Offset(-1, 3).Resize(1).Value = WorksheetFunction.Average(.Areas(iArea).Offset(, 1))
                Next
            End With
        Next
    End With
End Sub

Upvotes: 1

chillin
chillin

Reputation: 4486

This is possible in VBA (of course), but here's a formula approach if you want to try it.

Try putting this formula in cell D2 (assuming Pos is in column D):

=IF($B2<>"",COUNTIFS($A$2:$A2,A2,$B$2:$B2,"<>"),"")

And this formula in cell E2 (assuming Final Sc is in column E):

=IF($D2=1,AVERAGEIFS(C:C,A:A,$A2),"")

And then drag/fill the formulas down to the last row of your data.

Upvotes: 1

Samuel Hulla
Samuel Hulla

Reputation: 7109

This will work, presuming your table starts at cell A1

Private Sub fill_pos()

  Dim cell As Range
  Dim ws As Worksheet: Set ws = Sheets("Sheet1") 'replace Sheet1 with your Sheetname 
  Dim lr As Long: lr = ws.Cells(Rows.Count, 1).End(xlUp).Row ' last active row in column A
  Dim pos As Integer: pos = 0
  Dim avgcounter As Integer: avgcounter = 0 ' counter for average amount division
  Dim avgsum As Double: avgsum = 0 ' counter for sc
  Dim textrowpos As Long: textrowpos = 0 ' to keep track when to write finalsc
  Dim firstloop As Boolean: firstloop = True ' need to check for first loop, _
  because we cant get average withotu collecting data first

  For Each cell In ws.Range("A2:A" & lr) ' for each cell in userrange
      If IsEmpty(cell) Then
          pos = 0 ' new id, reset pos
      Else
          If Not IsEmpty(cell.Offset(, 1)) Then ' text found,
              pos = pos + 1
              cell.Offset(, 3) = pos ' update pos
              If Not firstloop Then
                  Cells(textrowpos, 5) = avgsum / avgcounter 
                  'got already data collected (since its not first loop), can write result
              End If
              firstloop = False ' toggle firstloop off
              avgsum = 0 ' reset all counters
              avgcounter = 0
              textrowpos = cell.Row ' save the row position to update future result
          Else
              If Not IsEmpty(cell.Offset(, 2)) Then
                  avgsum = avgsum + cell.Offset(, 2) ' add them to average
                  avgcounter = avgcounter + 1
              End If
          End If
      End If

      If (cell.Row = lr) Then
          Cells(textrowpos, 5) = avgsum / avgcounter 
          ' since list ends with empty cell, we need to update textrow pos one last time
      End If
  Next cell

End Sub

Yields the desired result:

enter image description here

But I highly doubt you'll understand the code without doing some coding yourself.
Honestly I genuinely contemplated whether I should even post the answer..

Upvotes: 0

Related Questions