| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Countif with multiple worksheets
I am trying to count the occurence of text that appear within a 12 month
rolling report. When i enter the criteria and the range I come up with the error #REF or #Value. I have tried the SUM(IF('January 2008:June 2008'!$B$4:$B$10,"Mindset/Intentions")) and the countif formula, but I still receive the same error message |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Countif with multiple worksheets
Here's a possible workaround:
=SUM(COUNTIF('January 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('February 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('March 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('April 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('May 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('June 2008'!B4:B10,"Mindset/Intentions"),) -- -SA "Countif error" wrote: I am trying to count the occurence of text that appear within a 12 month rolling report. When i enter the criteria and the range I come up with the error #REF or #Value. I have tried the SUM(IF('January 2008:June 2008'!$B$4:$B$10,"Mindset/Intentions")) and the countif formula, but I still receive the same error message |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Countif with multiple worksheets
Excel is very bad when it comes to 3D functionality
Here is a way of bypassing that http://www.mcgimpsey.com/excel/threedsumif.html note that unless you use the add-in morefunc you would need to create a list of all the sheet names in question -- Regards, Peo Sjoblom "Countif error" <Countif wrote in message ... I am trying to count the occurence of text that appear within a 12 month rolling report. When i enter the criteria and the range I come up with the error #REF or #Value. I have tried the SUM(IF('January 2008:June 2008'!$B$4:$B$10,"Mindset/Intentions")) and the countif formula, but I still receive the same error message |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Countif with multiple worksheets
I tried the following but the same error message pops up.
"StumpedAgain" wrote: Here's a possible workaround: =SUM(COUNTIF('January 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('February 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('March 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('April 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('May 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('June 2008'!B4:B10,"Mindset/Intentions"),) -- -SA "Countif error" wrote: I am trying to count the occurence of text that appear within a 12 month rolling report. When i enter the criteria and the range I come up with the error #REF or #Value. I have tried the SUM(IF('January 2008:June 2008'!$B$4:$B$10,"Mindset/Intentions")) and the countif formula, but I still receive the same error message |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Countif with multiple worksheets
Thanks for the help after I rearrange some of the characters, the formula
finally worked. I shorten the worksheet names to simplify the formula. This is the formula I used =SUM(COUNTIF(Jan!B4:B10,"Mindset/Intentions"),COUNTIF(Feb!B4:B10,"Mindset/Intentions"),COUNTIF(Mar!B4:B10,"Mindset/Intentions"),COUNTIF(April!B4:B10,"Mindset/Intentions"),COUNTIF(May!B4:B10,"Mindset/Intentions"),COUNTIF(June!B4:B10,"Mindset/Intentions")) "StumpedAgain" wrote: Here's a possible workaround: =SUM(COUNTIF('January 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('February 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('March 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('April 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('May 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('June 2008'!B4:B10,"Mindset/Intentions"),) -- -SA "Countif error" wrote: I am trying to count the occurence of text that appear within a 12 month rolling report. When i enter the criteria and the range I come up with the error #REF or #Value. I have tried the SUM(IF('January 2008:June 2008'!$B$4:$B$10,"Mindset/Intentions")) and the countif formula, but I still receive the same error message |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Countif with multiple worksheets
I tired the formula again but I reaaranged and took our some of the
characters. This is the formula I used =SUM(COUNTIF(Jan!B4:B10,"Mindset/Intentions"),COUNTIF(Feb!B4:B10,"Mindset/Intentions"),COUNTIF(Mar!B4:B10,"Mindset/Intentions"),COUNTIF(April!B4:B10,"Mindset/Intentions"),COUNTIF(May!B4:B10,"Mindset/Intentions"),COUNTIF(June!B4:B10,"Mindset/Intentions")) "StumpedAgain" wrote: Here's a possible workaround: =SUM(COUNTIF('January 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('February 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('March 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('April 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('May 2008'!B4:B10,"Mindset/Intentions"),COUNTIF('June 2008'!B4:B10,"Mindset/Intentions"),) -- -SA "Countif error" wrote: I am trying to count the occurence of text that appear within a 12 month rolling report. When i enter the criteria and the range I come up with the error #REF or #Value. I have tried the SUM(IF('January 2008:June 2008'!$B$4:$B$10,"Mindset/Intentions")) and the countif formula, but I still receive the same error message |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| countif function in multiple worksheets | New Users to Excel | |||
| Help with countif on multiple worksheets | Excel Worksheet Functions | |||
| Countif multiple worksheets | Excel Worksheet Functions | |||
| Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions | |||
| "countif" from multiple worksheets within workbook | Excel Discussion (Misc queries) | |||
Linear Mode
