COUNTA Q
 

I am trying to create a function that adds up the number of instances
in D35:J35 that are greater than 1 (effectively where cells have a
value other than blank or 0) in all sheets between Start and End. Note
I 52 sheets between these two. The formula below doesn't produce the
result I'm expecting. Have I gone wrong?

=COUNTA(Start:End!D35:J350)

COUNTA Q
 

Hi,

Some functions do not work in 3D array references while some do such as SUM.
Also, please note that this is not a COUNTA() problem, this is a COUNTAIF
problem. What you should do here is that in D36 enter the formula
=if(D351,1,0). Copy this till J36. Perform this on all sheets. Infact is
the range is the same for all the sheets i.e. D35:J35, then you can group
all the sheets and write this formula at once

Once you have done this, please use the SUM function in your consolidation
sheet

=sum(first sheet:last sheet!D35:J35)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Seanie" wrote in message
...
I am trying to create a function that adds up the number of instances
in D35:J35 that are greater than 1 (effectively where cells have a
value other than blank or 0) in all sheets between Start and End. Note
I 52 sheets between these two. The formula below doesn't produce the
result I'm expecting. Have I gone wrong?

=COUNTA(Start:End!D35:J350)

COUNTA Q
 

Thanks, that worked

COUNTA Q
 

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Seanie" wrote in message
...
Thanks, that worked

COUNTA Q
 

On Nov 5, 9:28*am, "Ashish Mathur" wrote:
Hi,

Some functions do not work in 3D array references while some do such as SUM.
Also, please note that this is not a COUNTA() problem, this is a COUNTAIF
problem. *What you should do here is that in D36 enter the formula
=if(D351,1,0). *Copy this till J36. *Perform this on all sheets. *Infact is
the range is the same for all the sheets i.e. D35:J35, then you can group
all the sheets and write this formula at once

Once you have done this, please use the SUM function in your consolidation
sheet

=sum(first sheet:last sheet!D35:J35)

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com


How do I do this "Infact is the range is the same for all the sheets
i.e. D35:J35, then you can group all the sheets and write this formula
at once"?

Thanks