Count if criteria is part of list

[partyof4]

Want to be able to count all value in particular range that have a
value that appear in list of multiple values

eg have spreadsheet all different types computers and what versions of
o/s and service pack they are running

eg
Sheet 1
A B

Windows XP Professional SP2 Dell Inc.
Windows XP Professional SP2 Dell Inc.
Windows 2000 Professional SP4 Compaq
Windows 2000 Professional SP4 Dell Inc.
Windows 2000 Professional SP4 Dell Inc.
Windows 98 sr2 IBM
Windows XP Professional SP2 Dell Inc.
Windows XP Professional SP2 Dell Inc.
Windows 2000 Server SP4 Dell Inc.
Windows 2003 Standard Server SP1 Dell Inc.
SCO Unix 5.0.6 Compaq


Sheet 2
Windows XP Professional SP2
Windows 2000 Server SP4
Windows 2003 Standard Server SP1
SCO Unix 5.0.6


What i need to count is how many machines are (Win XP, Win 2000 Server,
Win 2003 Server ) etc

 

[Guest]

In Sheet2 B1
=COUNTIF(Sheet1!A$1:A$11,A1)
and drag it down as required! Adjust A$11 as needed!

Regards,
Stefi



„partyof4†ezt írta:

 

[Guest]

Hi,

out in column C in every row 1.

your data started by me in cell A1, then for example in column D put
=SUMIF(A:A;A1;C:C)
for another PC use next criteria and so on

I don't say that this is the easy way.

Henrich

 

[Guest]

You can use sub totals to do this, just sort by column A and indicate that at
every change in Column A you want a count.

Click DATA in the menu and select SUBTOTALS.

 

[partyof4]

Sorry may need to give more info here

what I need is total of items on sheet one( eg this is only one branch
of 37 and need to be able to total all servers etc easily

partyof4 wrote:
Want to be able to count all value in particular range that have a
value that appear in list of multiple values

eg have spreadsheet all different types computers and what versions of
o/s and service pack they are running

eg
Sheet 1
A B
Windows XP Professional SP2 Dell Inc.
Windows XP Professional SP2 Dell Inc.
Windows 2000 Professional SP4 Compaq
Windows 2000 Professional SP4 Dell Inc.
Windows 98 sr2 IBM
Windows XP Professional SP2 Dell Inc.
Windows XP Professional SP2 Dell Inc.
Windows 2000 Server SP4 Dell Inc.
Windows 2003 Standard Server SP1 Dell Inc.
SCO Unix 5.0.6 Compaq

Total of Servers =
COUNTIF(a1:a11,Sheet2!A1)+COUNTIF(a1:a11,Sheet2!A2)+
COUNTIF(a1:a11,Sheet2!A3)

Total of Workstation =
COUNTIF(a1:a11,Sheet2!b1)+COUNTIF(a1:a11,Sheet2!b2)+
COUNTIF(a1:a11,Sheet2!b3)

Sheet 2
A B
Windows 2000 Server SP4 Windows XP Professional SP2
Windows 2003 Standard Server SP1 Windows 2000 Professional SP4
SCO Unix 5.0.6 Windows 98 sr2

This list of Servers and OS is always changing need to easily reference
this list
Instead of changing 37 separate formulas

Ideally would need formula that is
=countif(a1:a11,Sheet2!a1:a3)
Where Sheet2!a1:a3 is a changing list and can be updated on the fly