VBA add a criteria to a Sum function

[weefatb0b]

Hello, I have a macro, below which works great but I have been asked to add another criteria to it, this one does it for each blank cell in column C, what I have been asked for is to only sum column C if the value in column B is in Column I, I have tried various but cant work out how to do this, any ideas would be brilliant? Thanks in advance

Sub TOTALCOLUMNC()
    Dim ws As Worksheet
    Set ws = Worksheets("Position") 'define worksheet here

    Dim FirstCell As Range
    Set FirstCell = ws.Range("C2")

    Dim VeryLastCell As Range 'get very last cell as stop criteria
    Set VeryLastCell = ws.Cells(ws.Rows.Count, "I").End(xlUp)

    Do
        Dim LastCell As Range
        If FirstCell.Offset(1) = vbNullString Then 'test if there is only one cell to sum
            Set LastCell = FirstCell
        Else
            Set LastCell = FirstCell.End(xlDown)
        End If

        With LastCell.Offset(1, 0) 'this is the cell we want to write the sum
            .Value = Application.WorksheetFunction.Sum(ws.Range(FirstCell, LastCell))
            .Interior.Color = RGB(192, 192, 192)
        End With

        Set FirstCell = LastCell.Offset(2, 0)

    Loop While FirstCell.Row < VeryLastCell.Row
 
End Sub

 

[weefatb0b]

Head of Service 1 value is in Column B and the lookup column is column I

 

[Akuini]

what I have been asked for is to only sum column C if the value in column B is in Column I,

Head of Service 1 value is in Column B and the lookup column is column I

But in image in post #5, col I is blank?
Sorry, I'm confused. Can you explain what you're trying to do using the sample & show what the result should look like?

 

[weefatb0b]

Sorry my bad, copy and paste error, this is what it does look like

Position Number	Position Title	Establishment Count	Occupied	Vacant	Vacant %	Unit/Dept	Serv. Area	HOS
1234567	Position 1	0.93	0.00	0.93		Unit 1	Service Area 1	Head of Service 1
1234567	Position 2	0.00	0.96	-0.96		Unit 1	Service Area 1	Head of Service 1
1234567	Position 3	2.10	0.77	1.33		Unit 1	Service Area 1	Head of Service 1
1234567	Position 4	0.00	1.00	-1.00		Unit 1	Service Area 1	Head of Service 1
1234567	Position 5	0.00	0.05	-0.05		Unit 1	Service Area 1	Head of Service 1
1234567	Position 6	1.62	0.62	1.00		Unit 1	Service Area 1	Head of Service 1
1234567	Position 7	0.00	1.00	-1.00		Unit 1	Service Area 1	Head of Service 1
1234567	Position 8	1.00	1.00	0.00		Unit 1	Service Area 1	Head of Service 1
1234567	Position 9	4.70	0.00	4.70		Unit 1	Service Area 1	Head of Service 1
1234567	Position 10	0.00	0.41	-0.41		Unit 1	Service Area 1	Head of Service 1
1234567	Position 11	0.00	2.41	-2.41		Unit 1	Service Area 1	Head of Service 1
1234567	Position 12	0.00	0.41	-0.41		Unit 1	Service Area 1	Head of Service 1
1234567	Position 13	3.27	0.87	2.40		Unit 1	Service Area 1	Head of Service 1
1234567	Position 14	0.00	0.87	-0.87		Unit 1	Service Area 1	Head of Service 1
1234567	Position 15	0.00	0.87	-0.87		Unit 1	Service Area 1	Head of Service 1
1234567	Position 16	0.00	0.87	-0.87		Unit 1	Service Area 1	Head of Service 1
1234567	Position 17	1.03	0.00	1.03		Unit 1	Service Area 1	Head of Service 1
1234567	Position 18	0.00	0.41	-0.41		Unit 1	Service Area 1	Head of Service 1
1234567	Position 19	0.62	0.00	0.62		Unit 1	Service Area 1	Head of Service 1
						Unit 1	Service Area 1	Head of Service 1
1234567	Position 1	1.04	1.14	-0.10		Unit 2	Service Area 1	Head of Service 1
1234567	Position 2	0.08	0.31	-0.23		Unit 2	Service Area 1	Head of Service 1
1234567	Position 3	3.00	1.00	2.00		Unit 2	Service Area 1	Head of Service 1
1234567	Position 4	0.00	1.00	-1.00		Unit 2	Service Area 1	Head of Service 1
1234567	Position 5	0.00	1.00	-1.00		Unit 2	Service Area 1	Head of Service 1
1234567	Position 6	6.28	1.29	4.99		Unit 2	Service Area 1	Head of Service 1
1234567	Position 7	0.00	0.00	0.00		Unit 2	Service Area 1	Head of Service 1
1234567	Position 8	0.00	1.81	-1.81		Unit 2	Service Area 1	Head of Service 1
1234567	Position 9	0.00	0.51	-0.51		Unit 2	Service Area 1	Head of Service 1
1234567	Position 10	1.81	0.87	0.94		Unit 2	Service Area 1	Head of Service 1
1234567	Position 11	0.00	0.87	-0.87		Unit 2	Service Area 1	Head of Service 1
1234567	Position 12	0.33	0.49	-0.16		Unit 2	Service Area 1	Head of Service 1
1234567	Position 13	0.77	0.00	0.77		Unit 2	Service Area 1	Head of Service 1
1234567	Position 14	0.77	0.38	0.39		Unit 2	Service Area 1	Head of Service 1
						Unit 2	Service Area 1	Head of Service 1
	Head of Service 1	Sum Here if B37 is in column I	Sum Here if B37 is in column I	Sum Here if B37 is in column I

and then loop all the way to the bottom of the data where each value in Column B i.e. HOS Name is in Column I. The Blank rows will be completed with a Macro in Step 2 , I want to do the first bit first, so the sum does not include the Unit Totals, as this would give an inflated total.

 

[Akuini]

Why col I has only "Head of Service 1"?

 

[weefatb0b]

It doesnt, there will be various other Heads of Service, there is over 750 roes of data,

 

[Akuini]

It doesnt, there will be various other Heads of Service, there is over 750 roes of data,

Do you mean that in column B, there are multiple instances of "Head of Service"? And for every row that contains "Head of Service" (in col B), you need to perform the summation as you described?

How are we supposed to identify the rows where "Head of Service" appears in column B? Do their actual values always start with "Head of Service"?"

 

[weefatb0b]

Sorry, I really havent explained this very well. I will try again, my thinking is for every value in Column B, where it equals the value in column I then sum up all the values in Column C, from the row above value in column B and put value in Cell next to value in Column B I have anonomised the data, these will not be the values actually on my report, and I have not included all 800 odd rows, but between each Head of Service row in Column B there will be data to be summed. Hopefully this is a bit clearer,

Position Number	Position Title	Establishment Count	Occupied	Vacant	Vacant %	Unit/Dept	Serv. Area	HOS
Position 1		0.93	0.00	0.93		Unit 1	Service Area 1	Head of Service 1
Position 2		0.00	0.96	-0.96		Unit 1	Service Area 1	Head of Service 1
Position 3		2.10	0.77	1.33		Unit 1	Service Area 1	Head of Service 1
Position 4		0.00	1.00	-1.00		Unit 1	Service Area 1	Head of Service 1
Position 5		0.00	0.05	-0.05		Unit 1	Service Area 1	Head of Service 1
Position 6		1.62	0.62	1.00		Unit 1	Service Area 1	Head of Service 1
Position 7		0.00	1.00	-1.00		Unit 1	Service Area 1	Head of Service 1
Position 8		1.00	1.00	0.00		Unit 1	Service Area 1	Head of Service 1
Position 9		4.70	0.00	4.70		Unit 1	Service Area 1	Head of Service 1
Position 10		0.00	0.41	-0.41		Unit 1	Service Area 1	Head of Service 1
Position 11		0.00	2.41	-2.41		Unit 1	Service Area 1	Head of Service 1
Position 12		0.00	0.41	-0.41		Unit 1	Service Area 1	Head of Service 1
Position 13		3.27	0.87	2.40		Unit 1	Service Area 1	Head of Service 1
Position 14		0.00	0.87	-0.87		Unit 1	Service Area 1	Head of Service 1
Position 15		0.00	0.87	-0.87		Unit 1	Service Area 1	Head of Service 1
Position 16		0.00	0.87	-0.87		Unit 1	Service Area 1	Head of Service 1
Position 17		1.03	0.00	1.03		Unit 1	Service Area 1	Head of Service 1
Position 18		0.00	0.41	-0.41		Unit 1	Service Area 1	Head of Service 1
Position 19		0.62	0.00	0.62		Unit 1	Service Area 1	Head of Service 1
Unit Total						Unit 1	Service Area 1	Head of Service 1
Position 1		1.04	1.14	-0.10		Unit 2	Service Area 1	Head of Service 1
Position 2		0.08	0.31	-0.23		Unit 2	Service Area 1	Head of Service 1
Position 3		3.00	1.00	2.00		Unit 2	Service Area 1	Head of Service 1
Position 4		0.00	1.00	-1.00		Unit 2	Service Area 1	Head of Service 1
Position 5		0.00	1.00	-1.00		Unit 2	Service Area 1	Head of Service 1
Position 6		6.28	1.29	4.99		Unit 2	Service Area 1	Head of Service 1
Position 7		0.00	0.00	0.00		Unit 2	Service Area 1	Head of Service 1
Position 8		0.00	1.81	-1.81		Unit 2	Service Area 1	Head of Service 1
Position 9		0.00	0.51	-0.51		Unit 2	Service Area 1	Head of Service 1
Position 10		1.81	0.87	0.94		Unit 2	Service Area 1	Head of Service 1
Position 11		0.00	0.87	-0.87		Unit 2	Service Area 1	Head of Service 1
Position 12		0.33	0.49	-0.16		Unit 2	Service Area 1	Head of Service 1
Position 13		0.77	0.00	0.77		Unit 2	Service Area 1	Head of Service 1
Position 14		0.77	0.38	0.39		Unit 2	Service Area 1	Head of Service 1
Unit Total						Unit 2	Service Area 1	Head of Service 1
	Head of Service 1							Head of Service 1
	Head of Service 2							Head of Service 2
	Head of Service 3							Head of Service 3
	Head of Service 4							Head of Service 4
	Head of Service 5							Head of Service 5
	Head of Service 6							Head of Service 6
	Head of Service 7							Head of Service 7
	Head of Service 8							Head of Service 8
	Head of Service 9							Head of Service 9
	Head of Service 10							Head of Service 10

 

[weefatb0b]

Ive managed to get the below working

Dim ws As Worksheet
Set ws = Worksheets("Position")
ws.Range("C52") = Application.WorksheetFunction.SUMIF(ws.Range("I2:I51"), "*" & ws.Range("B52") & "*", ws.Range("C2:C51"))
End Sub

But this only does it for cell C52

 

[weefatb0b]

I've also tried this:

Dim lastRow As Long
Dim i As Long

lastRow = Cells(Rows.Count, "B").End(xlUp).Row

For i = 2 To lastRow
If Cells(i, "B").Value = Cells(i, "I").Value Then
Cells(i, "C").Value = WorksheetFunction.Sum(Range(Cells(i, "C"), Cells(i, "C").End(xlDown)))
End If
Next i
End Sub


But this returns the value 1 in each of the cells in C adjacent to the Value in B

 

[weefatb0b]

So I have managed to get the below working to an extent

  Dim lastRow As Long
   Dim i As Long
    
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    For i = 2 To lastRow
    If Cells(i, "B").Value = Cells(i, "I").Value Then
            Cells(i, "C").Value = WorksheetFunction.Sum(Range(Cells(i, "C"), Cells(i, "C").End(xlUp)))
        End If
    Next i
End Sub

However It does not recognize where the Value match between column B & I changes and includes the total row of the match above?