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.

> 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).

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

On Mar 25, 3:34*pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
>
> I think you may be looking for something like this...
>
> =SUMPRODUCT((B2:B1000="Office1)*(C2:C1000="Found))
>
> Rick Rothstein (MVP - Excel).- Hide quoted text -
>
> - Show quoted text -

On Mar 25, 5:08*am, brett <planetbr...@gmail.com> wrote:
> 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.