SUMPRODUCT help needed
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.
sounds like
=SUMPRODUCT((Sheet1!B2:B100="restored")*(Sheet1!C2:C100=Sheet2!A2:A100))
adjust the ranges as required
not a professional, just trying to assist.....
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" <bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com>
wrote in message
news:bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> bradgrafelman
> ------------------------------------------------------------------------
> bradgrafelman's Profile:
> http://www.excelforum.com/member.php...o&userid=24228
> View this thread: http://www.excelforum.com/showthread...hreadid=378442
>
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" <bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com>
wrote in message
news:bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> bradgrafelman
> ------------------------------------------------------------------------
> bradgrafelman's Profile:
> http://www.excelforum.com/member.php...o&userid=24228
> View this thread: http://www.excelforum.com/showthread...hreadid=378442
>
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" <bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com>
wrote in message
news:bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> bradgrafelman
> ------------------------------------------------------------------------
> bradgrafelman's Profile:
> http://www.excelforum.com/member.php...o&userid=24228
> View this thread: http://www.excelforum.com/showthread...hreadid=378442
>
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" <bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com>
wrote in message
news:bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> bradgrafelman
> ------------------------------------------------------------------------
> bradgrafelman's Profile:
> http://www.excelforum.com/member.php...o&userid=24228
> View this thread: http://www.excelforum.com/showthread...hreadid=378442
>
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" <bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com>
wrote in message
news:bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> bradgrafelman
> ------------------------------------------------------------------------
> bradgrafelman's Profile:
> http://www.excelforum.com/member.php...o&userid=24228
> View this thread: http://www.excelforum.com/showthread...hreadid=378442
>
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" <bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com>
wrote in message
news:bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> bradgrafelman
> ------------------------------------------------------------------------
> bradgrafelman's Profile:
> http://www.excelforum.com/member.php...o&userid=24228
> View this thread: http://www.excelforum.com/showthread...hreadid=378442
>
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" <bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com>
wrote in message
news:bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> bradgrafelman
> ------------------------------------------------------------------------
> bradgrafelman's Profile:
> http://www.excelforum.com/member.php...o&userid=24228
> View this thread: http://www.excelforum.com/showthread...hreadid=378442
>
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" <bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com>
wrote in message
news:bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> bradgrafelman
> ------------------------------------------------------------------------
> bradgrafelman's Profile:
> http://www.excelforum.com/member.php...o&userid=24228
> View this thread: http://www.excelforum.com/showthread...hreadid=378442
>
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" <bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com>
wrote in message
news:bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> bradgrafelman
> ------------------------------------------------------------------------
> bradgrafelman's Profile:
> http://www.excelforum.com/member.php...o&userid=24228
> View this thread: http://www.excelforum.com/showthread...hreadid=378442
>
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" <bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com>
wrote in message
news:bradgrafelman.1qjiqc_1118624706.228@excelforum-nospam.com...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> bradgrafelman
> ------------------------------------------------------------------------
> bradgrafelman's Profile:
> http://www.excelforum.com/member.php...o&userid=24228
> View this thread: http://www.excelforum.com/showthread...hreadid=378442
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks