Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif using tab range
This seems intuitive to me, but I keep getting an error. I would like to
apply a countif statement to a column in a range of tabs within a workbook, i.e. =COUNTIF('TAB1:TAB5'!F:F,A2). Is there anyway to do this without writing a function for each individual column? Thanks in advance. DEI |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif using tab range
i'll assume the A2 you refer to is on sheet "Summary"
Unfortunately, COUNTIF is not a 3d function. You can do it with a helper cell though. Find a cell that is blank on all your sheets (Say, Z1). Now, select all your sheets you want to apply the countif in, and then type in Z1 =COUNTIF('TAB1:TAB5'!F:F,'Summary'!A2) You can this hide this cell (again, having all sheets selected). Now, in the cell you originally wanted the COUNTIF formula in =SUM('TAB1:TAB5'!Z1) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DEI" wrote: This seems intuitive to me, but I keep getting an error. I would like to apply a countif statement to a column in a range of tabs within a workbook, i.e. =COUNTIF('TAB1:TAB5'!F:F,A2). Is there anyway to do this without writing a function for each individual column? Thanks in advance. DEI |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif using tab range
Luke M wrote...
.... Unfortunately, COUNTIF is not a 3d function. You can do it with a helper cell though. .... =COUNTIF('TAB1:TAB5'!F:F,'Summary'!A2) .... This is a typo. As you correctly point out, COUNTIF doesn't support 3D references, so the formula above will return #VALUE! errors. You'd need to use =COUNTIF(F:F,'Summary'!A2) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif using tab range
Doh! Thanks for finding that mistake Harlan.
-- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Harlan Grove" wrote: Luke M wrote... .... Unfortunately, COUNTIF is not a 3d function. You can do it with a helper cell though. .... =COUNTIF('TAB1:TAB5'!F:F,'Summary'!A2) .... This is a typo. As you correctly point out, COUNTIF doesn't support 3D references, so the formula above will return #VALUE! errors. You'd need to use =COUNTIF(F:F,'Summary'!A2) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif in a value range? | Excel Discussion (Misc queries) | |||
COUNTIF with age range | Excel Worksheet Functions | |||
Countif between a range? | Excel Worksheet Functions | |||
Using countif with a name of a range | Excel Worksheet Functions | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions |