Formula to find text and count it within an entire workbook

[Carrie_Loos via OfficeKB.com]

I have a workbook where there is nothing consistent or any named ranges. I
need to make a summary sheet and count everytime I have the word cable in the
entire workbook in column B. I have tried different variations of countif,
lookups and find but have not come up with a combination that works. My
restraint is that I can't have a macro for this workbook. I really need some
help, example below.


A B
1 Cable
2 FSR
3 FRK
4 Wind Meter
5 Laptop


Thanks in advance,
Carrie

 

[Eduardo]

Hi,

=COUNTIF($a$1:$H$5000,"*Cable*")

if the data is in a different sheet called Data use

=COUNTIF(Data!$a$1:$H$5000,"*Cable*")

change range to fit your needs

 

[Carrie_Loos via OfficeKB.com]

Hi Eduardo, thanks but I want to collect this information throughout the
entire workbook, there are several worksheets and none of them are named
consistantly. The formula would be unmanageable and after a few changes
unrelyable tryining to insert a worksheet name into the formula.

Hi,

=COUNTIF($a$1:$H$5000,"*Cable*")

if the data is in a different sheet called Data use

=COUNTIF(Data!$a$1:$H$5000,"*Cable*")

change range to fit your needs

I have a workbook where there is nothing consistent or any named ranges. I
need to make a summary sheet and count everytime I have the word cable in the

[quoted text clipped - 12 lines]

Thanks in advance,
Carrie

 

[ryguy7272]

I'm sure there's a way to do it with VBA. I have to think about it. If you
want a quick solution do the following:
Ctrl + F > enter 'Cable' into Find What > click Options > Within Workbook >
Find All.



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Hi Eduardo, thanks but I want to collect this information throughout the
entire workbook, there are several worksheets and none of them are named
consistantly. The formula would be unmanageable and after a few changes
unrelyable tryining to insert a worksheet name into the formula.

Hi,

=COUNTIF($a$1:$H$5000,"*Cable*")

if the data is in a different sheet called Data use

=COUNTIF(Data!$a$1:$H$5000,"*Cable*")

change range to fit your needs

I have a workbook where there is nothing consistent or any named ranges. I
need to make a summary sheet and count everytime I have the word cable in the

[quoted text clipped - 12 lines]

Thanks in advance,
Carrie

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/201004/1

.

 

[K_Macd]

Don't forget to group select sheets that you want to find an expression in -
otherwise will only search in the active sheet.

I note that you want to count the occassions that cable appears -ultimately
I believe you should be standardising the input across all sheets then using
data validation limiting future entry to items on a lookup list. Note that
the list can be on its own sheet and can be referred to as long as it has had
a range name applied to it

--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"

I'm sure there's a way to do it with VBA. I have to think about it. If you
want a quick solution do the following:
Ctrl + F > enter 'Cable' into Find What > click Options > Within Workbook >
Find All.



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Hi Eduardo, thanks but I want to collect this information throughout the
entire workbook, there are several worksheets and none of them are named
consistantly. The formula would be unmanageable and after a few changes
unrelyable tryining to insert a worksheet name into the formula.

Hi,

=COUNTIF($a$1:$H$5000,"*Cable*")

if the data is in a different sheet called Data use

=COUNTIF(Data!$a$1:$H$5000,"*Cable*")

change range to fit your needs

I have a workbook where there is nothing consistent or any named ranges. I
need to make a summary sheet and count everytime I have the word cable in the
[quoted text clipped - 12 lines]
Thanks in advance,
Carrie

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/201004/1

.