| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTA Q
Thanks, that worked
|
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTA Q
You are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Seanie" wrote in message ... Thanks, that worked |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counta | Excel Discussion (Misc queries) | |||
| COUNTA() | Excel Discussion (Misc queries) | |||
| COUNTA AND | Excel Discussion (Misc queries) | |||
| using COUNTA | Excel Discussion (Misc queries) | |||
| COUNTA Function not working =COUNTA(C3:C69,"NH") | Excel Worksheet Functions | |||
Linear Mode
