Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF across worksheets
Good morning --
I'm shooting for a COUNTIF formula that will count text occurances across multiple worksheets. I can get the formula to work if the data is on the same worksheet but as soon as I go to multiple ones, it fails and I get a '#VALUE!' error. Here's the formula: =COUNTIF(First:Last!A1,"Y") Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF across worksheets
On Sun, 21 Feb 2010 05:59:02 -0800, pdberger
wrote: Good morning -- I'm shooting for a COUNTIF formula that will count text occurances across multiple worksheets. I can get the formula to work if the data is on the same worksheet but as soon as I go to multiple ones, it fails and I get a '#VALUE!' error. Here's the formula: =COUNTIF(First:Last!A1,"Y") Thanks in advance. COUNTIF does not work with 3D references. You may be able to use something like: =SUMPRODUCT(COUNTIF(INDIRECT("'"&A4:A7&"'!A1"),"Y" )) where A1:A4 contains the names of your worksheets. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF across worksheets
On Sun, 21 Feb 2010 05:59:02 -0800, pdberger
wrote: Good morning -- I'm shooting for a COUNTIF formula that will count text occurances across multiple worksheets. I can get the formula to work if the data is on the same worksheet but as soon as I go to multiple ones, it fails and I get a '#VALUE!' error. Here's the formula: =COUNTIF(First:Last!A1,"Y") Thanks in advance. Have a look he https://www.microsoft.com/communitie...r=US&sloc=&p=1 Hope this helps / Lars-Åke |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF across worksheets
Hi,
You can't create a 3D formula with Countif, it doesn't support it so here's another apptoach Create a list of your sheet names in a column ( I used B1:B4) like this First Sheet3 Sheet4 last Then the formula =SUMPRODUCT(COUNTIF(INDIRECT("'"&B1:B4&"'!A1"),"Y" )) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "pdberger" wrote: Good morning -- I'm shooting for a COUNTIF formula that will count text occurances across multiple worksheets. I can get the formula to work if the data is on the same worksheet but as soon as I go to multiple ones, it fails and I get a '#VALUE!' error. Here's the formula: =COUNTIF(First:Last!A1,"Y") Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif from different worksheets | Excel Worksheet Functions | |||
countif in many worksheets | Excel Worksheet Functions | |||
Countif over several worksheets | Excel Worksheet Functions | |||
=COUNTIF across Worksheets | Excel Worksheet Functions | |||
countif across worksheets | Excel Worksheet Functions |