Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountIf
Is it possible to set a CountIf criteria across more than 1 worksheet? I
want to find all occurances of the word John across all 5 worksheets. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountIf
Not directly but you can use it in combination with other functions.
If your sheet names are unique and don't follow any sort of sequential naming convention then you have to list the sheet names in a range of cells. Let's assume you list the sheet names in the range J1:J5. =SUMPRODUCT(COUNTIF(INDIRECT("'"&J1:J5&"'!A:A"),"J ohn")) That will do a COUNTIF of column A for the sheets listed in J1:J5 for the string John. If your sheet names follow some sort of sequential naming convention ( Week1, Week2, Week3 etc.) then you can build this into the formula and not have to make a list of the sheet names. You can always put a separate COUNITF formula on each individual sheet in the same cell then do a SUM: =COUNTIF(A:A,"John") =SUM(Sheet1:Sheet5!B1) -- Biff Microsoft Excel MVP "ordnance1" wrote in message ... Is it possible to set a CountIf criteria across more than 1 worksheet? I want to find all occurances of the word John across all 5 worksheets. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountIf
On Wed, 24 Dec 2008 17:12:01 -0800, ordnance1
wrote: Is it possible to set a CountIf criteria across more than 1 worksheet? I want to find all occurances of the word John across all 5 worksheets. Download and install Laurent Longre's free morefunc.xll add-in and use the COUNTIF.3D function. 1. This does not seem to work in Excel 2007, but works in earlier versions. 2. The usual download site is intermittently non-functional; you may have to do a google search to find a good site. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum a countif | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |