Sum by using especial criteria

Kishan · Oct 17, 2018

Using Excel 2000
Hi,

In the column I got 7 numbers pattern secreted by vertical bar 
In the column I got count for each patterns
I want a get sum in the column E by using patterns "especial criteria" first 4 numbers. Example...
C6 Pattern is "0 | 0| 0 | 1 | 2 | 2 | 2" 
Where first 4 numbers are "0 | 0| 0 | 1" so sum of column D6 in the cell E6 =1 

C7 Pattern is "0 | 0| 0 | 2 | 1 | 2 | 2"
C8 Pattern is "0 | 0| 0 | 2 | 2 | 1 | 2"
C8 Pattern is "0 | 0| 0 | 2 | 2 | 2 | 1" 
Where first 4 numbers are "0 | 0| 0 | 2" so sum of column D7+D8+D9 in the cell E9 =1

And will be summed all by using the same criteria 

Example data

Book1

A

B

C

D

E

F

G

1

2

3

4

Patt

Count

Sum

5

Patt

60

6

0 | 0 | 0 | 1 | 2 | 2 | 2

1

7

0 | 0 | 0 | 2 | 1 | 2 | 2

0

8

0 | 0 | 0 | 2 | 2 | 1 | 2

0

9

0 | 0 | 0 | 2 | 2 | 2 | 1

1

10

0 | 0 | 1 | 0 | 2 | 2 | 2

1

11

0 | 0 | 1 | 1 | 1 | 2 | 2

0

12

0 | 0 | 1 | 1 | 2 | 1 | 2

2

13

0 | 0 | 1 | 1 | 2 | 2 | 1

3

5

14

0 | 0 | 1 | 2 | 0 | 2 | 2

0

15

0 | 0 | 1 | 2 | 1 | 1 | 2

2

16

0 | 0 | 1 | 2 | 1 | 2 | 1

0

17

0 | 0 | 1 | 2 | 2 | 0 | 2

1

18

0 | 0 | 1 | 2 | 2 | 1 | 1

2

19

0 | 0 | 1 | 2 | 2 | 2 | 0

0

5

20

0 | 0 | 2 | 0 | 1 | 2 | 2

1

21

0 | 0 | 2 | 0 | 2 | 1 | 2

0

22

0 | 0 | 2 | 0 | 2 | 2 | 1

1

2

23

0 | 0 | 2 | 1 | 0 | 2 | 2

1

24

0 | 0 | 2 | 1 | 1 | 1 | 2

1

25

0 | 0 | 2 | 1 | 1 | 2 | 1

3

26

0 | 0 | 2 | 1 | 2 | 0 | 2

0

27

0 | 0 | 2 | 1 | 2 | 1 | 1

2

28

0 | 0 | 2 | 1 | 2 | 2 | 0

0

7

29

0 | 0 | 2 | 2 | 0 | 1 | 2

0

30

0 | 0 | 2 | 2 | 0 | 2 | 1

2

31

0 | 0 | 2 | 2 | 1 | 0 | 2

0

32

0 | 0 | 2 | 2 | 1 | 1 | 1

0

33

0 | 0 | 2 | 2 | 1 | 2 | 0

0

34

0 | 0 | 2 | 2 | 2 | 0 | 1

0

35

0 | 0 | 2 | 2 | 2 | 1 | 0

0

2

36

0 | 1 | 0 | 0 | 2 | 2 | 2

0

37

0 | 1 | 0 | 1 | 1 | 2 | 2

1

38

0 | 1 | 0 | 1 | 2 | 1 | 2

2

39

0 | 1 | 0 | 1 | 2 | 2 | 1

3

6

40

0 | 1 | 0 | 2 | 0 | 2 | 2

1

41

0 | 1 | 0 | 2 | 1 | 1 | 2

0

42

0 | 1 | 0 | 2 | 1 | 2 | 1

1

43

0 | 1 | 0 | 2 | 2 | 0 | 2

0

44

0 | 1 | 0 | 2 | 2 | 1 | 1

1

45

0 | 1 | 0 | 2 | 2 | 2 | 0

1

4

46

0 | 1 | 1 | 0 | 1 | 2 | 2

1

47

0 | 1 | 1 | 0 | 2 | 1 | 2

0

48

0 | 1 | 1 | 0 | 2 | 2 | 1

0

1

49

0 | 1 | 1 | 1 | 0 | 2 | 2

1

50

0 | 1 | 1 | 1 | 1 | 1 | 2

3

51

0 | 1 | 1 | 1 | 1 | 2 | 1

4

52

0 | 1 | 1 | 1 | 2 | 0 | 2

1

53

0 | 1 | 1 | 1 | 2 | 1 | 1

2

54

0 | 1 | 1 | 1 | 2 | 2 | 0

2

13

55

0 | 1 | 1 | 2 | 0 | 1 | 2

2

56

0 | 1 | 1 | 2 | 0 | 2 | 1

2

57

0 | 1 | 1 | 2 | 1 | 0 | 2

0

58

0 | 1 | 1 | 2 | 1 | 1 | 1

3

59

0 | 1 | 1 | 2 | 1 | 2 | 0

3

60

0 | 1 | 1 | 2 | 2 | 0 | 1

1

61

0 | 1 | 1 | 2 | 2 | 1 | 0

1

12

62

0 | 1 | 2 | 0 | 0 | 2 | 2

0

63

64

Sheet1

Thank you in advance

Regards,
Kishan

Special-K99 · Oct 17, 2018

Your example data implies it will only ever be made up of 0s, 1s or 2s and will contain at least 4 digits to add up. Is that the case? If not you need to specify what the other numbers are.

If it's only 0s 1s or 2s then just use

in D6
=MID(C6,1,1)+mid(c6,5,1)+mid(c6,9,1)+mid(c6,13,1)

Kishan · Oct 17, 2018

Special-K99 said:
Your example data implies it will only ever be made up of 0s, 1s or 2s and will contain at least 4 digits to add up. Is that the case? If not you need to specify what the other numbers are.

If it's only 0s 1s or 2s then just use

in D6
=MID(C6,1,1)+mid(c6,5,1)+mid(c6,9,1)+mid(c6,13,1)

Hi Special-K99, I don't need sum of first 4 numbers, I want to sum column D using unique criteria of first 4 numbers and the D sum in column in E

Thank you
Kishan

Kishan · Oct 17, 2018

Kishan said:
Using Excel 2000
Hi,

In the column I got 7 numbers pattern secreted by vertical bar 
In the column I got count for each patterns
I want a get sum in the column E by using patterns "especial criteria" first 4 numbers. Example...
C6 Pattern is "0 | 0| 0 | 1 | 2 | 2 | 2" 
Where first 4 numbers are "0 | 0| 0 | 1" so sum of column D6 in the cell E6 =1 

C7 Pattern is "0 | 0| 0 | 2 | 1 | 2 | 2"
C8 Pattern is "0 | 0| 0 | 2 | 2 | 1 | 2"
C8 Pattern is "0 | 0| 0 | 2 | 2 | 2 | 1" 
Where first 4 numbers are "0 | 0| 0 | 2" so sum of column D7+D8+D9 in the cell E9 =1

And will be summed all by using the same criteria

Hi,

Ok here is a simple expiation, 
As long as first 4 numbers are the same of pattern column C 
Sum the count of the columns D
And sum result show in the column E 

May this helps

Regards,
Kishan

MickG · Oct 17, 2018

Try this:-

Code:

[COLOR="Navy"]Sub[/COLOR] MG17Oct43
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Txt = Left(Dn.Value, 16)
[COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
    .Add Txt, Array(Dn.Offset(, 2), Dn.Offset(, 1).Value)
[COLOR="Navy"]Else[/COLOR]
    Q = .Item(Txt)
    [COLOR="Navy"]Set[/COLOR] Q(0) = Dn.Offset(, 2)
    Q(1) = Q(1) + Dn.Offset(, 1).Value
    .Item(Txt) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]

[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
     .Item(K)(0).Value = .Item(K)(1)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Kishan · Oct 17, 2018

MickG said:

Try this:-

Code:

[COLOR=navy]Sub[/COLOR] MG17Oct43
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Q [COLOR=navy]As[/COLOR] Variant, Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
Txt = Left(Dn.Value, 16)
[COLOR=navy]If[/COLOR] Not .Exists(Txt) [COLOR=navy]Then[/COLOR]
    .Add Txt, Array(Dn.Offset(, 2), Dn.Offset(, 1).Value)
[COLOR=navy]Else[/COLOR]
    Q = .Item(Txt)
    [COLOR=navy]Set[/COLOR] Q(0) = Dn.Offset(, 2)
    Q(1) = Q(1) + Dn.Offset(, 1).Value
    .Item(Txt) = Q
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]

[COLOR=navy]Dim[/COLOR] K [COLOR=navy]As[/COLOR] Variant
 [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] .keys
     .Item(K)(0).Value = .Item(K)(1)
[COLOR=navy]Next[/COLOR] K
[COLOR=navy]End[/COLOR] With

[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

MickG, Amazed!! Spot on solution

Thank you for your time and help

Have a good week ahead

Kind Regards,
Kishan

MickG · Oct 18, 2018

You're very welcome

Sum by using especial criteria

Kishan

Well-known Member

Excel Facts

Special-K99

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

MickG

MrExcel MVP

Kishan

Well-known Member

MickG

MrExcel MVP

Similar threads

Forum statistics

Share this page

Sum by using especial criteria

Kishan

Well-known Member

Excel Facts

Special-K99

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

MickG

MrExcel MVP

Kishan

Well-known Member

MickG

MrExcel MVP

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