Home |
Search |
Today's Posts |
#1
|
|||
|
|||
search value in a table and sum another column
I have a table of say 3 columns. The first column lists the part numbers ,
second column lists the serial number and the third column lists whether the part test was a success or failure (text). I want to search in the table of a given part number and return the total number of times the part number test was a success. I have tried vlookup, but it stops as soon as it gets a search match. But the value can appear multiple times in the column and for each matched row I want to look at the third column and see if the value is "success" and if it is I want to count it. Any help is appreciated. Thanks. NM |
#2
|
|||
|
|||
Hi,
Try =SUMPRODUCT((A1:A100="PART NO.")*(C1:C100="SUCCESS")) Where A1:A100 has the part numbers and C1:C100 has the part test result. Replace "PART NO." with the part no. If the part no. is alphanumeric, then place it within quotes and if not place it without quotes. Regards Govind. a931048 wrote: I have a table of say 3 columns. The first column lists the part numbers , second column lists the serial number and the third column lists whether the part test was a success or failure (text). I want to search in the table of a given part number and return the total number of times the part number test was a success. I have tried vlookup, but it stops as soon as it gets a search match. But the value can appear multiple times in the column and for each matched row I want to look at the third column and see if the value is "success" and if it is I want to count it. Any help is appreciated. Thanks. NM |
#3
|
|||
|
|||
Thanks a lot. I just saw a post about array formulas, which I did not knew
earlier. But that one has to use Ctrl+Shift+Enter after entering the function. This function takes care of that too. Thanks again. It works perfect. NM "Govind" wrote: Hi, Try =SUMPRODUCT((A1:A100="PART NO.")*(C1:C100="SUCCESS")) Where A1:A100 has the part numbers and C1:C100 has the part test result. Replace "PART NO." with the part no. If the part no. is alphanumeric, then place it within quotes and if not place it without quotes. Regards Govind. a931048 wrote: I have a table of say 3 columns. The first column lists the part numbers , second column lists the serial number and the third column lists whether the part test was a success or failure (text). I want to search in the table of a given part number and return the total number of times the part number test was a success. I have tried vlookup, but it stops as soon as it gets a search match. But the value can appear multiple times in the column and for each matched row I want to look at the third column and see if the value is "success" and if it is I want to count it. Any help is appreciated. Thanks. NM |
#4
|
|||
|
|||
Maybe...
=SUMPRODUCT(--(A2:A100="Part Number"),--(C2:C100="success")) "a931048" wrote in message ... I have a table of say 3 columns. The first column lists the part numbers , second column lists the serial number and the third column lists whether the part test was a success or failure (text). I want to search in the table of a given part number and return the total number of times the part number test was a success. I have tried vlookup, but it stops as soon as it gets a search match. But the value can appear multiple times in the column and for each matched row I want to look at the third column and see if the value is "success" and if it is I want to count it. Any help is appreciated. Thanks. NM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|