Macro to count and sub total columns

Sunline · Aug 21, 2011

Hello all , i was wanting to know if i could get a macro to count and subtotal cols CY:DH individually , looking at col CY there are two 1,s , return answer to col EN 2 ,
Col CY there is one number 2 , return answer to col EO 1 .
Col CY there is one 3= , return answer to col EP 1 .
Col CY there is one 4 , return answer to col EQ 1 .
Col CY there are no 5 or 6 , return answer to col ER and ES zero 0 .
Col CY there is one 7 , return answer to col ET 1 .
Col CY there are no 8 or 9 , return answer to col EU and EV zero 0 .
Col CY there is one 10 , return answer to col EW 1 .

Then move to col CZ and repeat above returning answers to cols EX:FG .
Then repeat for all cols in range CY:DH , ending up with 100 cols up that will have answers .
I have tried using count but as i have close to 180,000 rows comp cant process it all .
Notice blank cells and also may contain text .
Thanks .

Excel Workbook

CY

CZ

DA

DB

DC

DD

DE

DF

DG

DH

EN

EO

EP

EQ

ER

ES

ET

EU

EV

EW

EX

EY

EZ

FA

FB

FC

FD

FE

FF

FG

1

PL10

PL9

PL8

PL7

PL6

PL5

PL4

PL3

PL2

PL1

1/1=

2/2=

3/3=

4/4=

5/5=

6/6=

7/7=

8/8=

9/9=

10/10=

1/1=

2/2=

3/3=

4/4=

5/5=

6/6=

7/7=

8/8=

9/9=

10/10=

2

1

8

1

6

3

1

7

8

2

1

2

1

0

1

0

1

3

1

0

1

0

1

0

3

10

6

16

2

4

6

1

2

2=

2

1

0

1

0

1

3

1

0

1

0

1

0

4

*

8

2

3

2

1

0

1

0

1

3

1

0

1

0

1

0

5

4

9

1

5

1=

5

6

4

2

1

0

1

0

1

3

1

0

1

0

1

0

6

*

2

1

0

1

0

1

3

1

0

1

0

1

0

7

*

3

1

5

1

2

1

0

1

0

1

3

1

0

1

0

1

0

8

1

11

4

13

2

6

18

9

11

P

2

1

0

1

0

1

3

1

0

1

0

1

0

9

3=

2

5

2

6

1

6

8

5

7=

2

1

0

1

0

1

3

1

0

1

0

1

0

10

2

1=

5

3

1

4

3

4

7

3

2

1

0

1

0

1

3

1

0

1

0

1

0

11

*

1

2

4

2

1

2

1

0

1

0

1

3

1

0

1

0

1

0

12

*

1

2

5

2

6

1

14

1

2

1

0

1

0

1

3

1

0

1

0

1

0

13

7

1

3

1

9

5

8

7

1

5

2

1

0

1

0

1

3

1

0

1

0

1

0

14

*

1

8=

1

3

2

3

13=

2

7

2

1

0

1

0

1

3

1

0

1

0

1

0

15

*

2

1

0

1

0

1

3

1

0

1

0

1

0

Sheet1

Excel Workbook

CY

CZ

DA

DB

DC

DD

DE

DF

DG

DH

EN

EO

EP

EQ

ER

ES

ET

EU

EV

EW

EX

EY

EZ

FA

FB

FC

FD

FE

FF

FG

1

PL10

PL9

PL8

PL7

PL6

PL5

PL4

PL3

PL2

PL1

1/1=

2/2=

3/3=

4/4=

5/5=

6/6=

7/7=

8/8=

9/9=

10/10=

1/1=

2/2=

3/3=

4/4=

5/5=

6/6=

7/7=

8/8=

9/9=

10/10=

2

1

8

1

6

3

1

7

8

2

1

2

1

0

1

0

1

3

1

0

1

0

1

0

3

10

6

16

2

4

6

1

2

2=

2

1

0

1

0

1

3

1

0

1

0

1

0

4

*

8

2

3

2

1

0

1

0

1

3

1

0

1

0

1

0

5

4

9

1

5

1=

5

6

4

2

1

0

1

0

1

3

1

0

1

0

1

0

6

*

2

1

0

1

0

1

3

1

0

1

0

1

0

7

*

3

1

5

1

2

1

0

1

0

1

3

1

0

1

0

1

0

8

1

11

4

13

2

6

18

9

11

P

2

1

0

1

0

1

3

1

0

1

0

1

0

9

3=

2

5

2

6

1

6

8

5

7=

2

1

0

1

0

1

3

1

0

1

0

1

0

10

2

1=

5

3

1

4

3

4

7

3

2

1

0

1

0

1

3

1

0

1

0

1

0

11

*

1

2

4

2

1

2

1

0

1

0

1

3

1

0

1

0

1

0

12

*

1

2

5

2

6

1

14

1

2

1

0

1

0

1

3

1

0

1

0

1

0

13

7

1

3

1

9

5

8

7

1

5

2

1

0

1

0

1

3

1

0

1

0

1

0

14

*

1

8=

1

3

2

3

13=

2

7

2

1

0

1

0

1

3

1

0

1

0

1

0

15

*

2

1

0

1

0

1

3

1

0

1

0

1

0

Sheet1

Excel Workbook

CY

CZ

DA

DB

DC

DD

DE

DF

DG

DH

EN

EO

EP

EQ

ER

ES

ET

EU

EV

EW

EX

EY

EZ

FA

FB

FC

FD

FE

FF

FG

1

PL10

PL9

PL8

PL7

PL6

PL5

PL4

PL3

PL2

PL1

1/1=

2/2=

3/3=

4/4=

5/5=

6/6=

7/7=

8/8=

9/9=

10/10=

1/1=

2/2=

3/3=

4/4=

5/5=

6/6=

7/7=

8/8=

9/9=

10/10=

2

1

8

1

6

3

1

7

8

2

1

2

1

0

1

0

1

3

1

0

1

0

1

0

3

10

6

16

2

4

6

1

2

2=

2

1

0

1

0

1

3

1

0

1

0

1

0

4

*

8

2

3

2

1

0

1

0

1

3

1

0

1

0

1

0

5

4

9

1

5

1=

5

6

4

2

1

0

1

0

1

3

1

0

1

0

1

0

6

*

2

1

0

1

0

1

3

1

0

1

0

1

0

7

*

3

1

5

1

2

1

0

1

0

1

3

1

0

1

0

1

0

8

1

11

4

13

2

6

18

9

11

P

2

1

0

1

0

1

3

1

0

1

0

1

0

9

3=

2

5

2

6

1

6

8

5

7=

2

1

0

1

0

1

3

1

0

1

0

1

0

10

2

1=

5

3

1

4

3

4

7

3

2

1

0

1

0

1

3

1

0

1

0

1

0

11

*

1

2

4

2

1

2

1

0

1

0

1

3

1

0

1

0

1

0

12

*

1

2

5

2

6

1

14

1

2

1

0

1

0

1

3

1

0

1

0

1

0

13

7

1

3

1

9

5

8

7

1

5

2

1

0

1

0

1

3

1

0

1

0

1

0

14

*

1

8=

1

3

2

3

13=

2

7

2

1

0

1

0

1

3

1

0

1

0

1

0

15

*

2

1

0

1

0

1

3

1

0

1

0

1

0

Sheet1

Excel Workbook

CY

CZ

DA

DB

DC

DD

DE

DF

DG

DH

EN

EO

EP

EQ

ER

ES

ET

EU

EV

EW

EX

EY

EZ

FA

FB

FC

FD

FE

FF

FG

1

PL10

PL9

PL8

PL7

PL6

PL5

PL4

PL3

PL2

PL1

1/1=

2/2=

3/3=

4/4=

5/5=

6/6=

7/7=

8/8=

9/9=

10/10=

1/1=

2/2=

3/3=

4/4=

5/5=

6/6=

7/7=

8/8=

9/9=

10/10=

2

1

8

1

6

3

1

7

8

2

1

2

1

0

1

0

1

3

1

0

1

0

1

0

3

10

6

16

2

4

6

1

2

2=

2

1

0

1

0

1

3

1

0

1

0

1

0

4

*

8

2

3

2

1

0

1

0

1

3

1

0

1

0

1

0

5

4

9

1

5

1=

5

6

4

2

1

0

1

0

1

3

1

0

1

0

1

0

6

*

2

1

0

1

0

1

3

1

0

1

0

1

0

7

*

3

1

5

1

2

1

0

1

0

1

3

1

0

1

0

1

0

8

1

11

4

13

2

6

18

9

11

P

2

1

0

1

0

1

3

1

0

1

0

1

0

9

3=

2

5

2

6

1

6

8

5

7=

2

1

0

1

0

1

3

1

0

1

0

1

0

10

2

1=

5

3

1

4

3

4

7

3

2

1

0

1

0

1

3

1

0

1

0

1

0

11

*

1

2

4

2

1

2

1

0

1

0

1

3

1

0

1

0

1

0

12

*

1

2

5

2

6

1

14

1

2

1

0

1

0

1

3

1

0

1

0

1

0

13

7

1

3

1

9

5

8

7

1

5

2

1

0

1

0

1

3

1

0

1

0

1

0

14

*

1

8=

1

3

2

3

13=

2

7

2

1

0

1

0

1

3

1

0

1

0

1

0

15

*

2

1

0

1

0

1

3

1

0

1

0

1

0

Sheet1

Excel Workbook

CY

CZ

DA

DB

DC

DD

DE

DF

DG

DH

EN

EO

EP

EQ

ER

ES

ET

EU

EV

EW

EX

EY

EZ

FA

FB

FC

FD

FE

FF

FG

1

PL10

PL9

PL8

PL7

PL6

PL5

PL4

PL3

PL2

PL1

1/1=

2/2=

3/3=

4/4=

5/5=

6/6=

7/7=

8/8=

9/9=

10/10=

1/1=

2/2=

3/3=

4/4=

5/5=

6/6=

7/7=

8/8=

9/9=

10/10=

2

1

8

1

6

3

1

7

8

2

1

2

1

0

1

0

1

3

1

0

1

0

1

0

3

10

6

16

2

4

6

1

2

2=

2

1

0

1

0

1

3

1

0

1

0

1

0

4

*

8

2

3

2

1

0

1

0

1

3

1

0

1

0

1

0

5

4

9

1

5

1=

5

6

4

2

1

0

1

0

1

3

1

0

1

0

1

0

6

*

2

1

0

1

0

1

3

1

0

1

0

1

0

7

*

3

1

5

1

2

1

0

1

0

1

3

1

0

1

0

1

0

8

1

11

4

13

2

6

18

9

11

P

2

1

0

1

0

1

3

1

0

1

0

1

0

9

3=

2

5

2

6

1

6

8

5

7=

2

1

0

1

0

1

3

1

0

1

0

1

0

10

2

1=

5

3

1

4

3

4

7

3

2

1

0

1

0

1

3

1

0

1

0

1

0

11

*

1

2

4

2

1

2

1

0

1

0

1

3

1

0

1

0

1

0

12

*

1

2

5

2

6

1

14

1

2

1

0

1

0

1

3

1

0

1

0

1

0

13

7

1

3

1

9

5

8

7

1

5

2

1

0

1

0

1

3

1

0

1

0

1

0

14

*

1

8=

1

3

2

3

13=

2

7

2

1

0

1

0

1

3

1

0

1

0

1

0

15

*

2

1

0

1

0

1

3

1

0

1

0

1

0

Sheet1

GlennUK · Aug 24, 2011

Sunline said:
Im not sure why such a long function would be needed if i can do the same with this .
=SUM(COUNTIF(CY2:CY2282,{"1","1="}))

I was hoping to get a macro based on the above simple function to cover the 100 col answers required .

Thankyou for you help .

Because my function can be entered in one cell, and copied for the 100 col answers required. Yours can't.

Sunline · Aug 24, 2011

Good point , im getting zero 0 as answer when your two functions are Put into cells DI2 and DJ2 .
Thanks .

Sunline · Aug 24, 2011

More appoligies i didnt put the 1/1= or 2/2= into cells DI1 and DJ2 .
The function is returning an answer but not the correct one .
On a sample of 2281 rows i filtered for 1 or 1= and this is not matching what is in DI1 or DJ2 .
Thanks for your patience .

Sunline · Aug 24, 2011

Ok i finally figured it out i needed to change the 999 part of the function too 9999 .
Thanks for all your help GlennUK and your patience with me on this one .
Thankyou .

GlennUK · Aug 24, 2011

That's great that it's working now

My pleasure to help.

Macro to count and sub total columns

Sunline

Well-known Member

GlennUK

Well-known Member

Excel Facts

Sunline

Well-known Member

Sunline

Well-known Member

Sunline

Well-known Member

GlennUK

Well-known Member

Similar threads

Forum statistics

Share this page

Macro to count and sub total columns

Sunline

Well-known Member

GlennUK

Well-known Member

Excel Facts

Sunline

Well-known Member

Sunline

Well-known Member

Sunline

Well-known Member

GlennUK

Well-known 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