Advanced formula help needed

[brett]

Good Morning All,

I have a spreadsheet that I need to get create a count formula for.

Sheet1 is a listing of all the computer names that should exist
Sheet2 is a listing of all the computer names that have been
physically verified.

Sheet1 looks like the following:

Name Category Status
computer1 Office 1 Found
computer2 Office 2 Missing
computer3 Office 1 Missing


Sheet2 looks like the following:

Name Category Status
computer1 Office1 verified


I can generate the status in sheet1 by using this formula:
if(countif(sheet2_computer_names_range, sheet1_name_cell),"Found",
"Missing")

I need to have a formula that will count the number of computers in
office 1 that have been found.

Not sure if this is an easy fix that im just missing.

 

[Rick Rothstein]

I have a spreadsheet that I need to get create a count formula for.

Sheet1 is a listing of all the computer names that should exist
Sheet2 is a listing of all the computer names that have been
physically verified.

Sheet1 looks like the following:

Name Category Status
computer1 Office 1 Found
computer2 Office 2 Missing
computer3 Office 1 Missing


Sheet2 looks like the following:

Name Category Status
computer1 Office1 verified


I can generate the status in sheet1 by using this formula:
if(countif(sheet2_computer_names_range, sheet1_name_cell),"Found",
"Missing")

I need to have a formula that will count the number of computers in
office 1 that have been found.

Not sure if this is an easy fix that im just missing.

I think you may be looking for something like this...

=SUMPRODUCT((B2:B1000="Office1)*(C2:C1000="Found))

Rick Rothstein (MVP - Excel).

 

[Pete_UK]

A couple of typos in Rick's post - 2 missing quotes, so should be:

=SUMPRODUCT((B2:B1000="Office1")*(C2:C1000="Found"))

Hope this helps.

Pete

 

[joeu2004]

Sheet1 is a listing of all the computer names that
should exist
Sheet2 is a listing of all the computer names that
have been physically verified. [....]
I need to have a formula that will count the number
of computers in office 1 that have been found.

If you have XL2007 or later:

=COUNTIFS(Sheet2!$A$1:$A$1000,A1,Sheet2!$B$1:$B$1000,B1)

where A1 contains the computer name and B1 contains the office name.

If you have XL2003 or earlier:

=SUMPRODUCT((Sheet2!$A$1:$A$1000=A1)*(Sheet2!$B$1:$B$1000=B1))

The multiplication (*) acts like AND. We cannot use AND itself
effectively in this context.