count how often a name exists
I want to know how often a name (say jack) occurs in a spreadsheet.
The name has to be found in the range c6:f40 but......... i have 50 sheets. named 1, 2, 3, 4, .......50 and i want to know the sum of how often a name occurs in all sheets together. I think this has to be done in vba! Can someone help me? Greetings Hans |
count how often a name exists
Dim CntJack()
Dim sh as Worksheet, cnt as long for each sh in activeworkbook.worksheets cnt = cnt + application.countif(sh.range("C6:F40"),"Jack") Next msgbox "Jack cnt is " & cnt End Sub If the Jack could be part of a larger string use cnt = cnt + application.countif(sh.range("C6:F40"),"*Jack*") or for Jack at the beginning of a string cnt = cnt + application.countif(sh.range("C6:F40"),"Jack*") -- Regards, Tom Ogilvy "hans" wrote in message ... I want to know how often a name (say jack) occurs in a spreadsheet. The name has to be found in the range c6:f40 but......... i have 50 sheets. named 1, 2, 3, 4, .......50 and i want to know the sum of how often a name occurs in all sheets together. I think this has to be done in vba! Can someone help me? Greetings Hans |
count how often a name exists
Nope,
no VBA needed. Try a worksheetfunction: =COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:50"))&"! C6:F40");"jack") -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam hans wrote : I want to know how often a name (say jack) occurs in a spreadsheet. The name has to be found in the range c6:f40 but......... i have 50 sheets. named 1, 2, 3, 4, .......50 and i want to know the sum of how often a name occurs in all sheets together. I think this has to be done in vba! Can someone help me? Greetings Hans |
count how often a name exists
oops, that must be:
=SUMPRODUCT( COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:50"))&"!C 6:F40");"jack")) or =SUM( countif() ) entered as array function -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam keepITcool wrote : Nope, no VBA needed. Try a worksheetfunction: =COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:50"))&"! C6:F40");"jack") -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam hans wrote : I want to know how often a name (say jack) occurs in a spreadsheet. The name has to be found in the range c6:f40 but......... i have 50 sheets. named 1, 2, 3, 4, .......50 and i want to know the sum of how often a name occurs in all sheets together. I think this has to be done in vba! Can someone help me? Greetings Hans |
count how often a name exists
I want to do the same thing and have the results populated next to the name
on the summary sheet. What I am struggling with is how I list my sheet names in the function code. They are extremely different - they are names of projects - and there will be sheets added as well as removed from the workbook when projects close. Is there a way I can list the sheets to search for my specific resource name with a more generic sheet search rather than by typing in each individual sheet? I currently have 33 project tabs and 45 resources. "keepITcool" wrote: oops, that must be: =SUMPRODUCT( COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:50"))&"!C 6:F40");"jack")) or =SUM( countif() ) entered as array function -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam keepITcool wrote : Nope, no VBA needed. Try a worksheetfunction: =COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:50"))&"! C6:F40");"jack") -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam hans wrote : I want to know how often a name (say jack) occurs in a spreadsheet. The name has to be found in the range c6:f40 but......... i have 50 sheets. named 1, 2, 3, 4, .......50 and i want to know the sum of how often a name occurs in all sheets together. I think this has to be done in vba! Can someone help me? Greetings Hans |
count how often a name exists
Building on the indirect function.. I'd suggest putting a list of addresses in a range. like jack john mary sales_jan!c6:f40 sales_feb!c6:f40 sales_mar!c6:f40 then your formula could look like =sum(countif(indirect($a2:$a4);b$1)) enter as array function with ctrl shift enter then copy the formula down then copy the formulas right (careful while copying these single cell array formulas, you cant copy 'over' them as eg d1 to d1:d4 the address list CAN be build with CELL("address",cellref) functions -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Sukismomma wrote : I want to do the same thing and have the results populated next to the name on the summary sheet. What I am struggling with is how I list my sheet names in the function code. They are extremely different - they are names of projects - and there will be sheets added as well as removed from the workbook when projects close. Is there a way I can list the sheets to search for my specific resource name with a more generic sheet search rather than by typing in each individual sheet? I currently have 33 project tabs and 45 resources. "keepITcool" wrote: oops, that must be: =SUMPRODUCT( COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:50"))&"!C 6:F40");"jack")) or =SUM( countif() ) entered as array function -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam keepITcool wrote : Nope, no VBA needed. Try a worksheetfunction: =COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:50"))&"! C6:F40");"jack") -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam hans wrote : I want to know how often a name (say jack) occurs in a spreadsheet. The name has to be found in the range c6:f40 but......... i have 50 sheets. named 1, 2, 3, 4, .......50 and i want to know the sum of how often a name occurs in all sheets together. I think this has to be done in vba! Can someone help me? Greetings Hans |
All times are GMT +1. The time now is 10:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com