Advanced Count Formula

natheplas · Nov 2, 2022

Hello,

I am trying to count how many unique kits I have in excel. (A group of rows)

Each Kit Starts with a P in column G and ends with the last C, before the next P, in column G.

I need a formula that can identify the kits using the above logic and count how many unique kits there are please?

Can anyone help with this?

If you need more information, please let me know.

Ran the L2BB below:

Book9

A

B

C

D

E

F

1

Product Code

Product Description

Parent or Child

Kit Class

Qty

SO Optional

2

19605

A1 DDO170 KIT

P

S

0

3

02046

DDO 170-500 MOUNT FOOT

C

S

1

Y

4

16107

SHAFT KEYED 30MM MDF 1 & 2 (S)

C

S

1

Y

5

18718

A1 JM150 RH P400 KIT

P

S

0

6

03211

JM150 PCB 24V V2 RH 9T

C

S

1

N

7

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

8

03233

PP400N SOLENOID (S)

C

S

1

Y

9

18719

A1 JM150 LH P400 KIT

P

S

0

10

03212

JM150 PCB 24V V2 LH 9T

C

S

1

Y

11

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

12

03233

PP400N SOLENOID (S)

C

S

1

N

13

19605

A1 DDO170 KIT

P

S

0

14

03000V

DDO170 KE 3PH 60% M HP

C

S

1

Y

15

02046

DDO 170-500 MOUNT FOOT

C

S

1

Y

16

16107

SHAFT KEYED 30MM MDF 1 & 2 (S)

C

S

1

Y

17

19605

A1 DDO170 KIT

P

S

0

18

02046

DDO 170-500 MOUNT FOOT

C

S

1

Y

19

16107

SHAFT KEYED 30MM MDF 1 & 2 (S)

C

S

1

Y

20

18734

A1 JM200 RH P400 KIT

P

S

0

21

03207

JM200 MTR 3PH PCBL V2 RH 9T

C

S

1

Y

22

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

23

03233

PP400N SOLENOID (S)

C

S

1

Y

24

18717

A1 JM150 LH P300 KIT

P

S

0

25

03212

JM150 PCB 24V V2 LH 9T

C

S

1

N

26

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

N

27

03231

PP300N FUSE RELEASE UNIT

C

S

1

N

28

18717

A1 JM150 LH P300 KIT

P

S

0

29

03212

JM150 PCB 24V V2 LH 9T

C

S

1

N

30

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

N

31

03231

PP300N FUSE RELEASE UNIT

C

S

1

N

32

18717

A1 JM150 LH P300 KIT

P

S

0

33

03212

JM150 PCB 24V V2 LH 9T

C

S

1

N

34

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

N

35

03231

PP300N FUSE RELEASE UNIT

C

S

1

N

36

18717

A1 JM150 LH P300 KIT

P

S

0

37

03212

JM150 PCB 24V V2 LH 9T

C

S

1

N

38

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

N

39

03231

PP300N FUSE RELEASE UNIT

C

S

1

N

40

18730

A1 JM500 1Ph RH P400 KIT

P

S

0

41

03201

JM500 MTR 1PH PCB V2 RH

C

S

1

N

42

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

43

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

44

03233

PP400N SOLENOID (S)

C

S

1

Y

45

18730

A1 JM500 1Ph RH P400 KIT

P

S

0

46

03201

JM500 MTR 1PH PCB V2 RH

C

S

1

N

47

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

48

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

49

03233

PP400N SOLENOID (S)

C

S

1

Y

50

18730

A1 JM500 1Ph RH P400 KIT

P

S

0

51

03201

JM500 MTR 1PH PCB V2 RH

C

S

1

N

52

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

53

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

54

03233

PP400N SOLENOID (S)

C

S

1

Y

55

18730

A1 JM500 1Ph RH P400 KIT

P

S

0

56

03201

JM500 MTR 1PH PCB V2 RH

C

S

1

N

57

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

58

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

59

03233

PP400N SOLENOID (S)

C

S

1

Y

60

18717

A1 JM150 LH P300 KIT

P

S

0

61

03212

JM150 PCB 24V V2 LH 9T

C

S

1

N

62

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

N

63

03231

PP300N FUSE RELEASE UNIT

C

S

1

N

64

18716

A1 JM150 RH P300 KIT

P

S

0

65

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

N

66

18717

A1 JM150 LH P300 KIT

P

S

0

67

03212

JM150 PCB 24V V2 LH 9T

C

S

1

N

68

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

N

69

03231

PP300N FUSE RELEASE UNIT

C

S

1

N

70

18716

A1 JM150 RH P300 KIT

P

S

0

71

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

N

72

18731

A1 JM500 1Ph LH P400 KIT

P

S

0

73

03202

JM500 MTR 1PH PCB V2 LH

C

S

1

N

74

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

Y

75

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

76

03233

PP400N SOLENOID (S)

C

S

1

Y

77

18730

A1 JM500 1Ph RH P400 KIT

P

S

0

78

03201

JM500 MTR 1PH PCB V2 RH

C

S

1

N

79

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

80

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

81

03233

PP400N SOLENOID (S)

C

S

1

Y

82

18731

A1 JM500 1Ph LH P400 KIT

P

S

0

83

03202

JM500 MTR 1PH PCB V2 LH

C

S

1

N

84

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

Y

85

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

86

03233

PP400N SOLENOID (S)

C

S

1

Y

87

18730

A1 JM500 1Ph RH P400 KIT

P

S

0

88

03201

JM500 MTR 1PH PCB V2 RH

C

S

1

N

89

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

90

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

91

03233

PP400N SOLENOID (S)

C

S

1

Y

92

18717

A1 JM150 LH P300 KIT

P

S

0

93

03212

JM150 PCB 24V V2 LH 9T

C

S

1

N

94

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

N

95

03231

PP300N FUSE RELEASE UNIT

C

S

1

N

96

18716

A1 JM150 RH P300 KIT

P

S

0

97

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

N

98

18731

A1 JM500 1Ph LH P400 KIT

P

S

0

99

03202

JM500 MTR 1PH PCB V2 LH

C

S

1

N

100

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

Y

101

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

102

03233

PP400N SOLENOID (S)

C

S

1

Y

103

18730

A1 JM500 1Ph RH P400 KIT

P

S

0

104

03201

JM500 MTR 1PH PCB V2 RH

C

S

1

N

105

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

106

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

107

03233

PP400N SOLENOID (S)

C

S

1

Y

108

18717

A1 JM150 LH P300 KIT

P

S

0

109

03212

JM150 PCB 24V V2 LH 9T

C

S

1

N

110

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

N

111

03231

PP300N FUSE RELEASE UNIT

C

S

1

N

112

18716

A1 JM150 RH P300 KIT

P

S

0

113

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

N

114

18730

A1 JM500 1Ph RH P400 KIT

P

S

0

115

03201

JM500 MTR 1PH PCB V2 RH

C

S

1

N

116

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

117

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

118

03233

PP400N SOLENOID (S)

C

S

1

Y

119

18730

A1 JM500 1Ph RH P400 KIT

P

S

0

120

03201

JM500 MTR 1PH PCB V2 RH

C

S

1

N

121

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

122

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

123

03233

PP400N SOLENOID (S)

C

S

1

Y

124

18722

A1 JM500 3Ph RH P400 KIT

P

S

0

125

03205

JM500 MTR 3PH PCB V2 RH

C

S

1

N

126

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

127

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

128

03233

PP400N SOLENOID (S)

C

S

1

Y

129

18730

A1 JM500 1Ph RH P400 KIT

P

S

0

130

03201

JM500 MTR 1PH PCB V2 RH

C

S

1

N

131

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

132

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

133

03233

PP400N SOLENOID (S)

C

S

1

Y

134

18731

A1 JM500 1Ph LH P400 KIT

P

S

0

135

03202

JM500 MTR 1PH PCB V2 LH

C

S

1

N

136

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

Y

137

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

138

03233

PP400N SOLENOID (S)

C

S

1

Y

139

19606

A1 DDO260 KIT

P

S

0

140

16109

SHAFT KEYED 40MM MDF3/DDO

C

S

1

Y

141

07078

5 MTR OPTICAL SAFETY EDGE KIT

P

S

0

142

09009

C CHANNEL GALV

C

S

5

Y

143

07099

OPTICAL S/EDGE P3 RUBBER

C

S

5

Y

144

07094A

OPTICAL S/EDGE 5 CORE CURLY

C

S

1

Y

145

07095

OPTO S/EDGE JUNCTION BOX C/W

C

S

2

Y

146

07096

BUMPER TO SUIT P2 / P3 RUBBER

C

S

1

Y

147

08008

REDUCING PLUGS FOR P3 RUBBER

C

S

1

N

148

19605

A1 DDO170 KIT

P

S

0

149

03000V

DDO170 KE 3PH 60% M HP

C

S

1

Y

150

02046

DDO 170-500 MOUNT FOOT

C

S

1

Y

151

16107

SHAFT KEYED 30MM MDF 1 & 2 (S)

C

S

1

Y

152

19606

A1 DDO260 KIT

P

S

0

153

16109

SHAFT KEYED 40MM MDF3/DDO

C

S

1

Y

154

07078

5 MTR OPTICAL SAFETY EDGE KIT

P

S

0

155

09009

C CHANNEL GALV

C

S

5

Y

156

07099

OPTICAL S/EDGE P3 RUBBER

C

S

5

Y

157

07094A

OPTICAL S/EDGE 5 CORE CURLY

C

S

1

Y

158

07095

OPTO S/EDGE JUNCTION BOX C/W

C

S

2

Y

159

07096

BUMPER TO SUIT P2 / P3 RUBBER

C

S

1

Y

160

08008

REDUCING PLUGS FOR P3 RUBBER

C

S

1

N

161

19605

A1 DDO170 KIT

P

S

0

162

03000V

DDO170 KE 3PH 60% M HP

C

S

1

Y

163

02046

DDO 170-500 MOUNT FOOT

C

S

1

Y

164

16107

SHAFT KEYED 30MM MDF 1 & 2 (S)

C

S

1

Y

165

19606

A1 DDO260 KIT

P

S

0

166

16109

SHAFT KEYED 40MM MDF3/DDO

C

S

1

Y

167

07078

5 MTR OPTICAL SAFETY EDGE KIT

P

S

0

168

09009

C CHANNEL GALV

C

S

5

Y

169

07099

OPTICAL S/EDGE P3 RUBBER

C

S

5

Y

170

07094A

OPTICAL S/EDGE 5 CORE CURLY

C

S

1

Y

171

07095

OPTO S/EDGE JUNCTION BOX C/W

C

S

2

Y

172

07096

BUMPER TO SUIT P2 / P3 RUBBER

C

S

1

Y

173

08008

REDUCING PLUGS FOR P3 RUBBER

C

S

1

N

174

19605

A1 DDO170 KIT

P

S

0

175

03000V

DDO170 KE 3PH 60% M HP

C

S

1

Y

176

02046

DDO 170-500 MOUNT FOOT

C

S

1

Y

177

16107

SHAFT KEYED 30MM MDF 1 & 2 (S)

C

S

1

Y

178

18718

A1 JM150 RH P400 KIT

P

S

0

179

03211

JM150 PCB 24V V2 RH 9T

C

S

1

N

180

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

181

03233

PP400N SOLENOID (S)

C

S

1

Y

182

18730

A1 JM500 1Ph RH P400 KIT

P

S

0

183

03201

JM500 MTR 1PH PCB V2 RH

C

S

1

N

184

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

185

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

186

03233

PP400N SOLENOID (S)

C

S

1

Y

187

19601

A1 100M 94MM METAL ADAPTOR KIT

P

S

0

188

11349

ED59/100M 93MM METAL ADAPTOR

C

S

1

N

189

18730

A1 JM500 1Ph RH P400 KIT

P

S

0

190

03201

JM500 MTR 1PH PCB V2 RH

C

S

1

N

191

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

192

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

193

03233

PP400N SOLENOID (S)

C

S

1

Y

194

18722

A1 JM500 3Ph RH P400 KIT

P

S

0

195

03205

JM500 MTR 3PH PCB V2 RH

C

S

1

N

196

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

N

197

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

198

03233

PP400N SOLENOID (S)

C

S

1

Y

199

18731

A1 JM500 1Ph LH P400 KIT

P

S

0

200

03202

JM500 MTR 1PH PCB V2 LH

C

S

1

N

201

16106

JM 500 11T SPKT ONLY 1/2X5/16"

C

S

1

Y

202

16157

JM TO BRAKE JOIN CABLE 7MTRS

C

S

1

Y

203

03233

PP400N SOLENOID (S)

C

S

1

Y

Sheet3

ExceLoki · Nov 2, 2022

if "kit" is in the name of everyone in column B, then you could use

Excel Formula:

=COUNTIF(B:B,"*kit*")

or you could count the number of P in column C

Excel Formula:

=COUNTIF(C:C,"P")

natheplas · Nov 2, 2022

Thank you for coming back to me ExceLoki, it's a bit more complicated than that.

In the L2BB example below, the answer would be 2. As there are 3 kits, but there are 2 permutations of kits.

19605

02046

16107

Appears twice.

19605

02046

03000V

Appears once.

Are you able to help with that please?

Book9

I

J

K

L

M

N

1

Product Code

Product Description

Parent or Child

Kit Class

Qty

SO Optional

2

19605

A1 DDO170 KIT

P

S

0

3

02046

DDO 170-500 MOUNT FOOT

C

S

1

Y

4

16107

SHAFT KEYED 30MM MDF 1 & 2 (S)

C

S

1

Y

5

19605

A1 DDO170 KIT

P

S

0

6

02046

DDO 170-500 MOUNT FOOT

C

S

1

Y

7

03000V

DDO170 KE 3PH 60% M HP

C

S

1

Y

8

19605

A1 DDO170 KIT

P

S

0

9

02046

DDO 170-500 MOUNT FOOT

C

S

1

Y

10

16107

SHAFT KEYED 30MM MDF 1 & 2 (S)

C

S

1

Y

Sheet3

ExceLoki · Nov 2, 2022

in your example above P appears 3 times in column K, each with the same product code in column I.
this would count as 3, but you said it should be counted as 2.
is it based on the three product codes?
ex: if all three product codes are the same then it doesnt count again, but if one is different then it does count again?

trying to clarify what needs to be compared.

natheplas · Nov 2, 2022

Thanks Loki.

So each kit (product grouping) is denoted by a P and then 1 or more C's in column K (it won't always be 2 Cs)

Each kit ends with the last C.

Each new kit starts with the next P.

Each kit can have 1 P, but a different number of Cs.

The Ps and Cs are made up of product codes (names) in column I.

Each kit has a variation of different product codes in column I.

In the example above there are 3 kits. All 3 have one P and two Cs.

Two kits are made up of

19605
02046
16107

And 1 kit is made up of

19605
02046
03000V

So my count needs to be 2.

Counting not the number of kits, but the number of permutations.

Does that make sense? I know it's a lot.

Ideally I need to count the number of permutations, but also be able to remove any duplicate kits. Leaving a list of kits with unique permutations.

Thank you

Advanced Count Formula

natheplas

Board Regular

Excel Facts

ExceLoki

Well-known Member

natheplas

Board Regular

ExceLoki

Well-known Member

natheplas

Board Regular

Forum statistics

Share this page

Advanced Count Formula

natheplas

Board Regular

Excel Facts

ExceLoki

Well-known Member

natheplas

Board Regular

ExceLoki

Well-known Member

natheplas

Board Regular

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