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. |
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 |
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 |
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. |
All times are GMT +1. The time now is 09:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com