Sum by using especial criteria

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

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

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

And will be summed all by using the same criteria </SPAN></SPAN>

Example data</SPAN></SPAN>


Book1
ABCDEFG
1
2
3
4PattCountSum
5Patt6060
60 | 0 | 0 | 1 | 2 | 2 | 211
70 | 0 | 0 | 2 | 1 | 2 | 20
80 | 0 | 0 | 2 | 2 | 1 | 20
90 | 0 | 0 | 2 | 2 | 2 | 111
100 | 0 | 1 | 0 | 2 | 2 | 211
110 | 0 | 1 | 1 | 1 | 2 | 20
120 | 0 | 1 | 1 | 2 | 1 | 22
130 | 0 | 1 | 1 | 2 | 2 | 135
140 | 0 | 1 | 2 | 0 | 2 | 20
150 | 0 | 1 | 2 | 1 | 1 | 22
160 | 0 | 1 | 2 | 1 | 2 | 10
170 | 0 | 1 | 2 | 2 | 0 | 21
180 | 0 | 1 | 2 | 2 | 1 | 12
190 | 0 | 1 | 2 | 2 | 2 | 005
200 | 0 | 2 | 0 | 1 | 2 | 21
210 | 0 | 2 | 0 | 2 | 1 | 20
220 | 0 | 2 | 0 | 2 | 2 | 112
230 | 0 | 2 | 1 | 0 | 2 | 21
240 | 0 | 2 | 1 | 1 | 1 | 21
250 | 0 | 2 | 1 | 1 | 2 | 13
260 | 0 | 2 | 1 | 2 | 0 | 20
270 | 0 | 2 | 1 | 2 | 1 | 12
280 | 0 | 2 | 1 | 2 | 2 | 007
290 | 0 | 2 | 2 | 0 | 1 | 20
300 | 0 | 2 | 2 | 0 | 2 | 12
310 | 0 | 2 | 2 | 1 | 0 | 20
320 | 0 | 2 | 2 | 1 | 1 | 10
330 | 0 | 2 | 2 | 1 | 2 | 00
340 | 0 | 2 | 2 | 2 | 0 | 10
350 | 0 | 2 | 2 | 2 | 1 | 002
360 | 1 | 0 | 0 | 2 | 2 | 200
370 | 1 | 0 | 1 | 1 | 2 | 21
380 | 1 | 0 | 1 | 2 | 1 | 22
390 | 1 | 0 | 1 | 2 | 2 | 136
400 | 1 | 0 | 2 | 0 | 2 | 21
410 | 1 | 0 | 2 | 1 | 1 | 20
420 | 1 | 0 | 2 | 1 | 2 | 11
430 | 1 | 0 | 2 | 2 | 0 | 20
440 | 1 | 0 | 2 | 2 | 1 | 11
450 | 1 | 0 | 2 | 2 | 2 | 014
460 | 1 | 1 | 0 | 1 | 2 | 21
470 | 1 | 1 | 0 | 2 | 1 | 20
480 | 1 | 1 | 0 | 2 | 2 | 101
490 | 1 | 1 | 1 | 0 | 2 | 21
500 | 1 | 1 | 1 | 1 | 1 | 23
510 | 1 | 1 | 1 | 1 | 2 | 14
520 | 1 | 1 | 1 | 2 | 0 | 21
530 | 1 | 1 | 1 | 2 | 1 | 12
540 | 1 | 1 | 1 | 2 | 2 | 0213
550 | 1 | 1 | 2 | 0 | 1 | 22
560 | 1 | 1 | 2 | 0 | 2 | 12
570 | 1 | 1 | 2 | 1 | 0 | 20
580 | 1 | 1 | 2 | 1 | 1 | 13
590 | 1 | 1 | 2 | 1 | 2 | 03
600 | 1 | 1 | 2 | 2 | 0 | 11
610 | 1 | 1 | 2 | 2 | 1 | 0112
620 | 1 | 2 | 0 | 0 | 2 | 200
63
64
Sheet1


Thank you in advance</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
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)
 
Last edited:
Upvote 0
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)
Click to expand...
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</SPAN></SPAN>

Thank you</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Upvote 0
Kishan said:
Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

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

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

And will be summed all by using the same criteria </SPAN></SPAN>
Click to expand...
Hi,

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

May this helps

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Upvote 0
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
 
Upvote 0
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
Click to expand...
MickG, Amazed!! Spot on solution :cool:</SPAN></SPAN>

Thank you for your time and help</SPAN></SPAN>

Have a good week ahead</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan :)</SPAN></SPAN>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Sum with some criteria
2
Replies
12
Views
329
Peter_SSs
Peter_SSs
tabbytomo
  • Question Question
Sum cells with numbers and text
Replies
2
Views
389
Fluff
Fluff
ibmy
  • Solved
Find Number Pattern in Data - need 1 Column solution
Replies
7
Views
730
Peter_SSs
Peter_SSs
k3yn0t3
  • Question Question
Formula to Sum Based on Criteria
2
Replies
19
Views
1K
k3yn0t3
k3yn0t3
S
  • Solved
Sum column based on unique values in another column
2
Replies
11
Views
1K
samide2001
S

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top