VBA add a criteria to a Sum function

weefatb0b · Oct 6, 2023

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

VBA Code:

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

Akuini · Oct 10, 2023

You didn't answer my question:

Akuini said:
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"?"

But judging by your example in post #17, col B is either blank or has "Head of Service", is it correct?

weefatb0b · Oct 10, 2023

Akuini said:
You didn't answer my question:

But judging by your example in post #17, col B is either blank or has "Head of Service", is it correct?

No, there are many different values in Column B, Column B is mostly Job Titles except for the cells I have input previously to give me a sub total row for each Head of Service, if that makes sense. Head of Service is an anonomised value, its not the value used in the report

Akuini · Oct 10, 2023

weefatb0b said:
No, there are many different values in Column B, Column B is mostly Job Titles except for the cells I have input previously to give me a sub total row for each Head of Service, if that makes sense. Head of Service is an anonomised value, its not the value used in the report

So, how are we supposed to identify the rows where "Head of Service" appears in column B?

weefatb0b · Oct 10, 2023

Akuini said:
So, how are we supposed to identify the rows where "Head of Service" appears in column B?

VBA Code:

 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

As per post 20, I have managed to get this to work, to a certain extent, but it does not recognize where the match value in column B changes in Column I and gives me a higher than expected value, I have worked out that it is including the Sub Total value from the Column B match Value above, but cannot work out how to exclude that row....

Position Number	Position Title	Macro Total	Actual Total	Difference	Serv. Area	HOS
	Head of Service 1	38.42			Service Area 1	Head of Service 1
	Head of Service 2	282.52	244.10	38.42	Service Area 2	Head of Service 2

Akuini · Oct 10, 2023

weefatb0b said:
As per post 20, I have managed to get this to work, to a certain extent, but it does not recognize where the match value in column B changes in Column I and gives me a higher than expected value, I have worked out that it is including the Sub Total value from the Column B match Value above, but cannot work out how to exclude that row....

That doesn't answer my question.
Let's say Head of Service 1 is at B30, how the code should know that it is in B30?

weefatb0b · Oct 10, 2023

Akuini said:
That doesn't answer my question.
Let's say Head of Service 1 is at B30, how the code should know that it is in B30?

My thinking is it doesnt have to, happy to know if it does, it only needs to know if the value in Column B = Value in Column I then sum all the values in Column C above it?

Akuini · Oct 10, 2023

weefatb0b said:
My thinking is it doesnt have to, happy to know if it does, it only needs to know if the value in Column B = Value in Column I then sum all the values in Column C above it?

Sorry, I still don't understand your explanation. Hope somebody else will step in with a solution.

weefatb0b · Oct 10, 2023

Akuini said:
Sorry, I still don't understand your explanation. Hope somebody else will step in with a solution.

Hopefully the below helps more

Position Number	Position Title	Establishment Count	Occupied	Vacant	Vacant %	Unit/Dept	Serv. Area	HOS
584001	Position 1	0.93	0.00	0.93		Unit 1	Service Area 1	Head of service 1
584100	Position 2	0.00	0.96	-0.96		Unit 1	Service Area 1	Head of service 1
584220	Position 3	2.10	0.77	1.33		Unit 1	Service Area 1	Head of service 1
584222	Position 4	0.00	1.00	-1.00		Unit 1	Service Area 1	Head of service 1
584223	Position 5	0.00	0.05	-0.05		Unit 1	Service Area 1	Head of service 1
584320	Position 6	1.62	0.62	1.00		Unit 1	Service Area 1	Head of service 1
584321	Position 7	0.00	1.00	-1.00		Unit 1	Service Area 1	Head of service 1
584400	Position 8	1.00	1.00	0.00		Unit 1	Service Area 1	Head of service 1
584420	Position 9	4.70	0.00	4.70		Unit 1	Service Area 1	Head of service 1
584421	Position 10	0.00	0.41	-0.41		Unit 1	Service Area 1	Head of service 1
584423	Position 11	0.00	2.41	-2.41		Unit 1	Service Area 1	Head of service 1
584427	Position 12	0.00	0.41	-0.41		Unit 1	Service Area 1	Head of service 1
584460	Position 13	3.27	0.87	2.40		Unit 1	Service Area 1	Head of service 1
584461	Position 14	0.00	0.87	-0.87		Unit 1	Service Area 1	Head of service 1
584463	Position 15	0.00	0.87	-0.87		Unit 1	Service Area 1	Head of service 1
584464	Position 16	0.00	0.87	-0.87		Unit 1	Service Area 1	Head of service 1
584800	Position 17	1.03	0.00	1.03		Unit 1	Service Area 1	Head of service 1
584801	Position 18	0.00	0.41	-0.41		Unit 1	Service Area 1	Head of service 1
584920	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
580100	Position 1	1.04	1.14	-0.10		Unit 2	Service Area 1	Head of service 1
580170	Position 2	0.08	0.31	-0.23		Unit 2	Service Area 1	Head of service 1
580220	Position 3	3.00	1.00	2.00		Unit 2	Service Area 1	Head of service 1
580300	Position 4	0.00	1.00	-1.00		Unit 2	Service Area 1	Head of service 1
580307	Position 5	0.00	1.00	-1.00		Unit 2	Service Area 1	Head of service 1
580420	Position 6	6.28	1.29	4.99		Unit 2	Service Area 1	Head of service 1
580422	Position 7	0.00	0.00	0.00		Unit 2	Service Area 1	Head of service 1
580423	Position 8	0.00	1.81	-1.81		Unit 2	Service Area 1	Head of service 1
580424	Position 9	0.00	0.51	-0.51		Unit 2	Service Area 1	Head of service 1
580460	Position 10	1.81	0.87	0.94		Unit 2	Service Area 1	Head of service 1
580461	Position 11	0.00	0.87	-0.87		Unit 2	Service Area 1	Head of service 1
580800	Position 12	0.33	0.49	-0.16		Unit 2	Service Area 1	Head of service 1
580900	Position 13	0.77	0.00	0.77		Unit 2	Service Area 1	Head of service 1
580920	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
562010	Position 1	0.08	0.00	0.08		Unit 3	Service Area 1	Head of service 1
562022	Position 2	0.69	0.71	-0.02		Unit 3	Service Area 1	Head of service 1
562025	Position 3	0.79	0.62	0.17		Unit 3	Service Area 1	Head of service 1
562040	Position 4	0.41	0.00	0.41		Unit 3	Service Area 1	Head of service 1
						Unit 3	Service Area 1	Head of service 1
646100	Position 1	0.50	0.50	0.00		Unit 4	Service Area 1	Head of service 1
646420	Position 2	0.52	0.40	0.12		Unit 4	Service Area 1	Head of service 1
646480	Position 3	0.77	0.25	0.52		Unit 4	Service Area 1	Head of service 1
646481	Position 4	0.00	0.41	-0.41		Unit 4	Service Area 1	Head of service 1
						Unit 4	Service Area 1	Head of service 1
610001	Position 1	0.40	0.40	0.00		Unit 5	Service Area 1	Head of service 1
610100	Position 2	1.00	1.00	0.00		Unit 5	Service Area 1	Head of service 1
610420	Position 3	3.89	3.11	0.78		Unit 5	Service Area 1	Head of service 1
610702	Position 4	0.02	0.00	0.02		Unit 5	Service Area 1	Head of service 1
						Unit 5	Service Area 1	Head of service 1
	Head of service 1	If Value in Column B is in Column I then sum all values in Column C above					Service Area 1	Head of service 1
532001	Position 1	0.50	0.00	0.50		Init 1	Service Area 2	Head of service 2
532210	Position 2	1.00	0.00	0.00		Init 1	Service Area 2	Head of service 2
532340	Position 3	4.78	3.41	1.37		Init 1	Service Area 2	Head of service 2
532420	Position 4	44.07	33.61	10.46		Init 1	Service Area 2	Head of service 2
532440	Position 5	0.00	1.00	-1.00		Init 1	Service Area 2	Head of service 2
532620	Position 6	0.00	1.00	-1.00		Init 1	Service Area 2	Head of service 2
532750	Position 7	0.00	0.29	-0.29		Init 1	Service Area 2	Head of service 2
532800	Position 8	1.00	1.00	0.00		Init 1	Service Area 2	Head of service 2
						Init 1	Service Area 2	Head of service 2
519100	Position 1	0.96	1.00	-0.04		Init 2	Service Area 2	Head of service 2
519342	Position 2	0.00	1.00	-1.00		Init 2	Service Area 2	Head of service 2
519343	Position 3	2.00	0.00	2.00		Init 2	Service Area 2	Head of service 2
519440	Position 4	6.71	5.93	0.78		Init 2	Service Area 2	Head of service 2
519460	Position 5	1.81	1.75	0.06		Init 2	Service Area 2	Head of service 2
519800	Position 6	0.51	0.51	0.00		Init 2	Service Area 2	Head of service 2
519920	Position 7	0.78	1.33	-0.55		Init 2	Service Area 2	Head of service 2
						Init 2	Service Area 2	Head of service 2
533201	Position 1	0.60	0.60	0.00		Init 3	Service Area 2	Head of service 2
533202	Position 2	0.73	0.72	0.01		Init 3	Service Area 2	Head of service 2
533203	Position 3	2.00	2.00	0.00		Init 3	Service Area 2	Head of service 2
533204	Position 4	1.83	1.20	0.63		Init 3	Service Area 2	Head of service 2
533240	Position 5	16.64	16.29	0.35		Init 3	Service Area 2	Head of service 2
533245	Position 6	1.65	0.00	1.65		Init 3	Service Area 2	Head of service 2
533260	Position 7	1.29	1.29	0.00		Init 3	Service Area 2	Head of service 2
533280	Position 8	1.00	0.62	0.38		Init 3	Service Area 2	Head of service 2
						Init 3	Service Area 2	Head of service 2
534001	Position 1	0.50	1.00	-0.50		Init 4	Service Area 2	Head of service 2
534210	Position 2	1.00	1.00	0.00		Init 4	Service Area 2	Head of service 2
534320	Position 3	1.00	1.00	0.00		Init 4	Service Area 2	Head of service 2
534324	Position 4	0.00	1.00	-1.00		Init 4	Service Area 2	Head of service 2
534340	Position 5	3.48	0.62	2.86		Init 4	Service Area 2	Head of service 2
534341	Position 6	0.00	1.00	-1.00		Init 4	Service Area 2	Head of service 2
534342	Position 7	0.00	0.90	-0.90		Init 4	Service Area 2	Head of service 2
534343	Position 8	0.00	1.00	-1.00		Init 4	Service Area 2	Head of service 2
534420	Position 9	46.31	12.31	34.00		Init 4	Service Area 2	Head of service 2
534423	Position 10	0.00	6.54	-6.54		Init 4	Service Area 2	Head of service 2
534424	Position 11	0.00	4.23	-4.23		Init 4	Service Area 2	Head of service 2
534425	Position 12	0.00	4.72	-4.72		Init 4	Service Area 2	Head of service 2
534426	Position 13	0.00	7.02	-7.02		Init 4	Service Area 2	Head of service 2
534800	Position 14	0.84	0.72	0.12		Init 4	Service Area 2	Head of service 2
						Init 4	Service Area 2	Head of service 2
548001	Position 1	1.00	1.00	0.00		Init 5	Service Area 2	Head of service 2
548211	Position 2	2.08	1.00	1.08		Init 5	Service Area 2	Head of service 2
548212	Position 3	0.00	1.00	-1.00		Init 5	Service Area 2	Head of service 2
548400	Position 4	5.06	2.46	2.60		Init 5	Service Area 2	Head of service 2
548401	Position 5	0.00	0.77	-0.77		Init 5	Service Area 2	Head of service 2
548411	Position 6	3.90	3.89	0.01		Init 5	Service Area 2	Head of service 2
548412	Position 7	0.00	1.00	-1.00		Init 5	Service Area 2	Head of service 2
548413	Position 8	0.00	1.00	-1.00		Init 5	Service Area 2	Head of service 2
548420	Position 9	42.96	12.64	30.32		Init 5	Service Area 2	Head of service 2
548421	Position 10	0.00	1.16	-1.16		Init 5	Service Area 2	Head of service 2
548423	Position 11	0.00	3.32	-3.32		Init 5	Service Area 2	Head of service 2
548432	Position 12	0.00	7.01	-7.01		Init 5	Service Area 2	Head of service 2
548460	Position 13	0.26	0.25	0.01		Init 5	Service Area 2	Head of service 2
548620	Position 14	0.00	1.55	-1.55		Init 5	Service Area 2	Head of service 2
548621	Position 15	0.00	0.25	-0.25		Init 5	Service Area 2	Head of service 2
548623	Position 16	0.00	0.15	-0.15		Init 5	Service Area 2	Head of service 2
548800	Position 17	2.75	0.20	2.55		Unit 5	Service Area 2	Head of service 2
548801	Position 18	0.00	0.20	-0.20		Unit 5	Service Area 2	Head of service 2
548802	Position 19	0.00	0.77	-0.77		Unit 5	Service Area 2	Head of service 2
548803	Position 20	0.00	0.77	-0.77		Unit 5	Service Area 2	Head of service 2
548920	Position 21	1.16	0.72	0.44		Unit 5	Service Area 2	Head of service 2
						Unit 5	Service Area 2	Head of service 2
533101	Position 1	0.90	0.90	0.00		Unit 6	Service Area 2	Head of service 2
533102	Position 2	1.00	1.00	0.00		Unit 6	Service Area 2	Head of service 2
533103	Position 3	0.62	0.62	0.00		Unit 6	Service Area 2	Head of service 2
533104	Position 4	4.00	2.00	2.00		Unit 6	Service Area 2	Head of service 2
533105	Position 5	0.00	2.00	-2.00		Unit 6	Service Area 2	Head of service 2
533140	Position 6	29.61	19.64	9.97		Unit 6	Service Area 2	Head of service 2
533141	Position 7	0.00	11.32	-11.32		Unit 6	Service Area 2	Head of service 2
533150	Position 8	1.81	0.91	0.90		Unit 6	Service Area 2	Head of service 2
533160	Position 9	3.33	0.77	2.56		Unit 6	Service Area 2	Head of service 2
533161	Position 10	0.00	1.19	-1.19		Unit 6	Service Area 2	Head of service 2
533180	Position 11	0.67	0.62	0.05		Unit 6	Service Area 2	Head of service 2
						Unit 6	Service Area 2	Head of service 2
	Head of service 2	If Value in Column B is in Column I then sum all values in Column C above until Value in Column I changes					Service Area 2	Head of service 2

Which is basically what my macro in Post 20& 21 is doing, it works fine for Head of Service 1 and gives the correct total, but for Head of Service 2 it does not recognize the change in Column I and adds the Head of Service 1 total

Akuini · Oct 10, 2023

Try this:
I'm using the last of each Head of Service in column I to determine where the Head of Service is located in column B. I'm not sure if it fits your actual data.

VBA Code:

Sub try1()

Dim i As Long, j As Long
Dim va
Application.ScreenUpdating = False
va = Range("I1", Cells(Rows.Count, "I").End(xlUp))
For i = 2 To UBound(va, 1)
    j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) = va(i - 1, 1)
    i = i - 1
        Cells(i, "C") = WorksheetFunction.Sum(Range("C" & i - 1 & ":C" & j))
        Cells(i, "D") = WorksheetFunction.Sum(Range("D" & i - 1 & ":D" & j))
        Cells(i, "E") = WorksheetFunction.Sum(Range("E" & i - 1 & ":E" & j))
Next
Application.ScreenUpdating = True

End Sub

Example:

weefatb0b 1.xlsm

A

B

C

D

E

F

G

H

I

1

Position Number

Position Title

Establishment Count

Occupied

Vacant

Vacant %

Unit/Dept

Serv. Area

HOS

2

Position 1

1

2

3

Unit 1

Service Area 1

Head of Service 1

3

Position 2

1

2

3

Unit 1

Service Area 1

Head of Service 1

4

Position 18

1

2

3

Unit 1

Service Area 1

Head of Service 1

5

Position 19

1

2

3

Unit 1

Service Area 1

Head of Service 1

6

Unit Total

Unit 1

Service Area 1

Head of Service 1

7

Position 1

1

2

3

Unit 2

Service Area 1

Head of Service 1

8

Position 2

1

2

3

Unit 2

Service Area 1

Head of Service 1

9

Unit Total

Unit 2

Service Area 1

Head of Service 1

10

Head of Service 1

6

12

18

Head of Service 1

11

Head of Service 2

12

Position 1

1

2

3

Head of Service 2

13

Position 2

1

2

3

Head of Service 2

14

Position 3

1

2

3

Head of Service 2

15

Position 17

Head of Service 2

3

6

9

Head of Service 2

Sheet1

weefatb0b · Oct 10, 2023

Akuini said:
Try this:
I'm using the last of each Head of Service in column I to determine where the Head of Service is located in column B. I'm not sure if it fits your actual data.

VBA Code:

Sub try1() Dim i As Long, j As Long Dim va Application.ScreenUpdating = False va = Range("I1", Cells(Rows.Count, "I").End(xlUp)) For i = 2 To UBound(va, 1) j = i Do i = i + 1 If i > UBound(va, 1) Then Exit Do Loop While va(i, 1) = va(i - 1, 1) i = i - 1 Cells(i, "C") = WorksheetFunction.Sum(Range("C" & i - 1 & ":C" & j)) Cells(i, "D") = WorksheetFunction.Sum(Range("D" & i - 1 & ":D" & j)) Cells(i, "E") = WorksheetFunction.Sum(Range("E" & i - 1 & ":E" & j)) Next Application.ScreenUpdating = True End Sub

Example:

weefatb0b 1.xlsm
A B C D E F G H I
1 Position Number Position Title Establishment Count Occupied Vacant Vacant % Unit/Dept Serv. Area HOS
2 Position 1 1 2 3 Unit 1 Service Area 1 Head of Service 1
3 Position 2 1 2 3 Unit 1 Service Area 1 Head of Service 1
4 Position 18 1 2 3 Unit 1 Service Area 1 Head of Service 1
5 Position 19 1 2 3 Unit 1 Service Area 1 Head of Service 1
6 Unit Total Unit 1 Service Area 1 Head of Service 1
7 Position 1 1 2 3 Unit 2 Service Area 1 Head of Service 1
8 Position 2 1 2 3 Unit 2 Service Area 1 Head of Service 1
9 Unit Total Unit 2 Service Area 1 Head of Service 1
10 Head of Service 1 6 12 18 Head of Service 1
11 Head of Service 2
12 Position 1 1 2 3 Head of Service 2
13 Position 2 1 2 3 Head of Service 2
14 Position 3 1 2 3 Head of Service 2
15 Position 17 Head of Service 2 3 6 9 Head of Service 2
Sheet1

That is exactly what I need, and works exactly as I wanted, thank you very much, I appreciate the assistance and apologies for back and forth.

VBA add a criteria to a Sum function

weefatb0b

New Member

Akuini

Well-known Member

Excel Facts

weefatb0b

New Member

Akuini

Well-known Member

weefatb0b

New Member

Akuini

Well-known Member

weefatb0b

New Member

Akuini

Well-known Member

weefatb0b

New Member

Akuini

Well-known Member

weefatb0b

New Member

Similar threads

Forum statistics

Share this page

VBA add a criteria to a Sum function

New Member

Well-known Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock