count a value which appears multiple times in a worksheet across

[Rishikesh Khedkar]

SUBJECT: count a value which appears multiple times in a worksheet across
multiple worksheet.

Scenerio: I am a network engineer. I am done with an assessment at a client
who has many sites/closets. Now every site is a worksheet. Every column is a
device in the respective site. Every row is a parameter like switch name,
make model, qos enabled or not, total number of ports, no. of free ports etc
etc. There are 19 worksheets (Sites). The 20th worksheet is the summary. In
this sheet, I want to know 1. what are the different type(make model) of
switches available for all the sites in one column(Column A) and a count of
every type of switch(make model) in column B.

I achieved this using =COUNTIF('Sheet 1'!1:65536, "WS-C4503")+COUNTIF('Sheet
2'!1:65536, "WS-C4503")+COUNTIF('Sheet 3'!1:65536, "WS-C4503") ....till sheet
19

I want to know if there is a smarter way of doing this

Rishikesh Khedkar

 

[PvdG42]

SUBJECT: count a value which appears multiple times in a worksheet across
multiple worksheet.

Scenerio: I am a network engineer. I am done with an assessment at a
client
who has many sites/closets. Now every site is a worksheet. Every column is
a
device in the respective site. Every row is a parameter like switch name,
make model, qos enabled or not, total number of ports, no. of free ports
etc
etc. There are 19 worksheets (Sites). The 20th worksheet is the summary.
In
this sheet, I want to know 1. what are the different type(make model) of
switches available for all the sites in one column(Column A) and a count
of
every type of switch(make model) in column B.

I achieved this using =COUNTIF('Sheet 1'!1:65536,
"WS-C4503")+COUNTIF('Sheet
2'!1:65536, "WS-C4503")+COUNTIF('Sheet 3'!1:65536, "WS-C4503") ....till
sheet
19

I want to know if there is a smarter way of doing this

Rishikesh Khedkar

A properly designed database would make more sense for storing the data you
describe. It would be much simpler to obtain any needed summary data. If you
want to use Excel, you need to ask this question in a discussion group for
Excel. This is a .NET programming group.

To find the group you need:

As you appear to be using a web interface, start here:

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx

In the tree on the left, first open the English category, then open Office
and Desktop Applications. In Office and Desktop Applications, find and open
the Excel category. There, you'll find a group for your question.