SUMPRODUCT help needed

[bradgrafelman]

I believe I'll need to use the SUMPRODUCT function, but can't even begin
to guess how.

On Sheet1, I have two columns: Status and Server. The server numbers
are 'p066', 'p123', 'p223', and so on. The status is either two values:
"Not Restored" or "Restored".

On Sheet2, I have 3 columns: Server#, # Restored, and # Not Restored.
What I'd like to do is count all the rows that say "Restored" in column
B from Sheet1 but the C column from Sheet1 MUST have the value in the
"A" column from Sheet2.

I know that makes no sense whatsoever, so I've uploaded my spreadsheet
in hopes that someone can take a glance at it and help me out.

Any help would be greatly appreciated.


+-------------------------------------------------------------------+
|Filename: ezboard.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3483 |
+-------------------------------------------------------------------+

 

[Biff]

Hi!

You've got a slight problem!

I'm looking at your file.....

On sheet1 in the Server column, you're using a custom format of p###000.

C3 has a displayed value of p066 but the true underlying value of that cell
is 66.

On sheet2 in column A you have the server numbers. A3 has a value of p066.
This cell is formatted as GENERAL.

So, the problem is that p066 on sheet1 does not match p066 on sheet2.

You could come up with a formula to search for a specific number of chars
but the problem with that is some server numbers have 2 digits and some have
3 digits. For example:

=SUMPRODUCT(--(Sheet1!$B$3:$B$240=B1),--(ISNUMBER(SEARCH(RIGHT(A3,2),Sheet1!$C$3:$C$240))))

You need to use the same format style in each sheet for the server numbers.
Then the formula would be in sheet2 B3:

=SUMPRODUCT(--(Sheet1!$B$3:$B$240=B1),--(Sheet1!$C$3:$C$240=$A3))

Copy across to C3 then down to C67.

Another tip: On sheet2 get rid of the "#" in the headers of columns B and C.
Make them Restored and Not Restored.

Biff

"bradgrafelman" <[email protected]>
wrote in message
news:[email protected]...

 

[duane]

sounds like

=SUMPRODUCT((Sheet1!B2:B100="restored")*(Sheet1!C2:C100=Sheet2!A2:A100))

adjust the ranges as required