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

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

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

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