COUNTA Q

[Seanie]

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:J35>0)

 

[Ashish Mathur]

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(D35>1,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]

Thanks, that worked

 

[Ashish Mathur]

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Seanie]

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(D35>1,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