COUNTA Q

S

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)
 
A

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
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top