Finding the sum of the number of times an event occured

[ecy61sl]

Hi,

I'm trying to calculate the total/sum of the number of times an event occurred for a particular ID. For example, in the data sample below, in Column 4 I need to find the total number of times Column 3 is equal to 1 for a particular driverid from Column 1.

I've tried using various combinations of countifs and vlookup but was unsuccessful. Any solutions? TIA!

driverid​	distance​	ITC Flag​	Total ITC by Driver​
27029​	0.88​	0​
27029​	4.85​	1​
27029​	5​	0​
27029​	3.82​	0​
27029​	1.83​	1​
27029​	3.88​	0​
27029​	0.06​	1​
34295​	1.6​	0​
34295​	6.84​	0​
34295​	1.6​	1​
34295​	3.31​	1​
34295​	6.84​	1​
34295​	5.1​	0​
53863​	3.15​	0​
53863​	5.17​	0​
53863​	2.47​	1​
53863​	5.62​	0​
53863​	6.09​	0​
53863​	0.99​	1​

 

[AhoyNC]

Something like this?

Book1
	A	B	C	D
1	driverid	distance	ITC Flag	Total ITC by Driver
2	27029	0.88	0	4
3	27029	4.85	1	3
4	27029	5	0
5	27029	3.82	0
6	27029	1.83	1
7	27029	3.88	0
8	27029	0.06	1
9	34295	1.6	0	3
10	34295	6.84	0
11	34295	1.6	1	3
12	34295	3.31	1
13	34295	6.84	1
14	34295	5.1	0
15	53863	3.15	0	4
16	53863	5.17	0
17	53863	2.47	1	2
18	53863	5.62	0
19	53863	6.09	0
20	53863	0.99	1
Sheet1

Cell Formulas
Range	Formula
D2:D20	D2	=IF(COUNTIFS($A$2:A2,A2,$C$2:$C2,C2)=1,COUNTIFS($A$2:$A$20,A2,$C$2:$C$20,C2),"")

 

[ecy61sl]

Hi AhoyNC,

Thanks for the reply. The output I need is attached below. Hope my question is clearer now.

ITC Trips till 12.05 (version 1).xlsb
	A	B	C	D
1	driverid	distance	ITC Flag	Total ITC by Driver
2	27029	0.88	0	3
3	27029	4.85	1	3
4	27029	5	0	3
5	27029	3.82	0	3
6	27029	1.83	1	3
7	27029	3.88	0	3
8	27029	0.06	1	3
9	34295	1.6	0	3
10	34295	6.84	0	3
11	34295	1.6	1	3
12	34295	3.31	1	3
13	34295	6.84	1	3
14	34295	5.1	0	3
15	53863	3.15	0	2
16	53863	5.17	0	2
17	53863	2.47	1	2
18	53863	5.62	0	2
19	53863	6.09	0	2
20	53863	0.99	1	2
Sheet3

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this is what you want.

20 06 04.xlsm
	A	B	C	D
1	driverid	distance	ITC Flag	Total ITC by Driver
2	27029	0.88	0	3
3	27029	4.85	1	3
4	27029	5	0	3
5	27029	3.82	0	3
6	27029	1.83	1	3
7	27029	3.88	0	3
8	27029	0.06	1	3
9	34295	1.6	0	3
10	34295	6.84	0	3
11	34295	1.6	1	3
12	34295	3.31	1	3
13	34295	6.84	1	3
14	34295	5.1	0	3
15	53863	3.15	0	2
16	53863	5.17	0	2
17	53863	2.47	1	2
18	53863	5.62	0	2
19	53863	6.09	0	2
20	53863	0.99	1	2
Countifs

Cell Formulas
Range	Formula
D2:D20	D2	=COUNTIFS(A$2:A$20,A2,C$2:C$20,1)

 

[ecy61sl]

Hey Peter_SSs,

That works perfectly ? except I have 180k+ lines of data.

Do let me know if there's a faster way to do this. Read somewhere else about doing a concat first but that takes almost the same amount of time as well.

Thanks!

 

[AhoyNC]

If your ver. of Excel 365 has the FILTER function - Try:

Book1
	A	B	C	D
1	driverid	distance	ITC Flag	Total ITC by Driver
2	27029	0.88	0	3
3	27029	4.85	1	3
4	27029	5	0	3
5	27029	3.82	0	3
6	27029	1.83	1	3
7	27029	3.88	0	3
8	27029	0.06	1	3
9	34295	1.6	0	3
10	34295	6.84	0	3
11	34295	1.6	1	3
12	34295	3.31	1	3
13	34295	6.84	1	3
14	34295	5.1	0	3
15	53863	3.15	0	2
16	53863	5.17	0	2
17	53863	2.47	1	2
18	53863	5.62	0	2
19	53863	6.09	0	2
20	53863	0.99	1	2
Sheet1

Cell Formulas
Range	Formula
D2:D20	D2	=ROWS(FILTER($C$2:$C$20,($A$2:$A$20=A2)*($C$2:$C$20=1)))

 

[Peter_SSs]

Thanks for updating your profile.

except I have 180k+ lines of data.

Yes, that would make the COUNTIFS almost impossible to wait for.

I think you will find the FILTER method similarly slow (if you have that function)

If you must use formulas, then something like this would be a reasonable amount faster, but still may be too slow.
=IF(A2=A1,E1,COUNTIFS(A$2:A$185000,A2,C$2:C$185000,1))


If you don't need the results to be dynamic then this macro processed 185,000 rows for me in less than 1 second.
I have assumed that ITC Flag is always 0 or 1

Sub Count_Ones()
  Dim a As Variant
  Dim d As Object
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = d(a(i, 1)) + a(i, 3)
  Next i
  For i = 1 To UBound(a)
    a(i, 1) = d(a(i, 1))
  Next i
  Range("D2").Resize(UBound(a)).Value = a
End Sub