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 22, 2011

You say:

I have tried using count but as i have close to 180,000 rows comp cant process it all .

Are you saying that the subtotalling has to cover that entire range? ( i.e. count all the 1's and 1='s in range CY1:CY180000 etc etc )? Also, that would give 1 row of results ... but your output area has results for all rows ... explain what's going on there?

Sunline · Aug 22, 2011

Hi , firstly sorry about all the HTML tables i posted , should of been one only .
I did initially write 100 countifs i think is was but knew realistically it couldnt process all that over the entire 180,000 rows , hopefull thinking on my part . That was the ultimate result i wanted .
I wont be expecting it too run from CY1:CY180000 now though .
I greatfully have some macros from here which can cut the sheet down into currently 169 different sections which could contain just 18 rows or up too approx 50,000 rows per section , it varies .
The reason i want it too populate the answer to entire col is because i will then be filtering only a certain number from another col .

I may be asking way to much here im not sure . Even if this cant be done , having just being able to do too answering the cols EN:EW would be a start and maybe i could add more to macro if i can figure that out .
Thanks .

GlennUK · Aug 22, 2011

The reason i want it too populate the answer to entire col is because i will then be filtering only a certain number from another col .

... you'll have to explain that. You only have one row of results ... explain why you need them replicated up to 50000 times.

Sunline · Aug 22, 2011

Ok i think im starting to see your point here .
In col V i would then be filtering 1 or 1= , so therefore as your pointing out i probably only need answers to be put into the rows of cells that match 1 or 1= in col V .
Thanks .

GlennUK · Aug 22, 2011

You only need answers in one row ... the answers bear no relationship positionally to the source data. I don't know what you mean by "i probably only need answers to be put into the rows of cells that match 1 or 1= in col V . "

Sunline · Aug 22, 2011

Ok i only need answers in one row .
Thanks .

Sunline · Aug 23, 2011

Bump , i only need answers in one row .
Thanks for advice so far .

GlennUK · Aug 23, 2011

Have a look at this:

Excel Workbook

CY

CZ

DA

DB

DC

DD

DE

DF

DG

DH

DI

DJ

1

PL10

PL9

PL8

PL7

PL6

PL5

PL4

PL3

PL2

PL1

1/1=

2/2=

2

1

8

1

6

3

1

7

8

2

1

2

1

3

10

6

16

2

4

6

1

2

2=

4

2

8

2

3

5

4

9

1

5

1=

5

6

4

6

1=

Sheet

Sunline · Aug 23, 2011

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 .

Macro to count and sub total columns

Sunline

Well-known Member

Excel Facts

GlennUK

Well-known Member

Sunline

Well-known Member

GlennUK

Well-known Member

Sunline

Well-known Member

GlennUK

Well-known Member

Sunline

Well-known Member

Sunline

Well-known Member

GlennUK

Well-known Member

Sunline

Well-known Member

Similar threads

Forum statistics

Share this page

Macro to count and sub total columns

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

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