Thanks:  0
Likes:  0

# Thread: compare 2 colums of data and sum value on 3rd

1. I need a formula to compare names in column a with letters in column b and if a match to sum the value in c.

a b c

bill x 22
fred c 11
greg x 10
bill x 11

I need to sum column c when a=bill,b=x
I should have a result of 33 with this example.
I have tried several formulas but cannot get this result. I would appreciate any help! Thanks!

2. On 2002-05-12 16:44, jbrown wrote:
I need a formula to compare names in column a with letters in column b and if a match to sum the value in c.

a b c

bill x 22
fred c 11
greg x 10
bill x 11

I need to sum column c when a=bill,b=x
I should have a result of 33 with this example.
I have tried several formulas but cannot get this result. I would appreciate any help! Thanks!
=SUMPRODUCT((\$A\$2:\$A\$5=E1)*(\$B\$2:\$B\$5=E2),(\$C\$2:\$C\$5))

where E1 houses a value like bill and E2 a value like x.

3. {=SUM((A1:A4="bill")*(B1:B4="x")*C1:C4)}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination.

4. My columns are on sheet 1. How do I reference the sheet in this sum product formula?

5. EITHER PUT ON THE SAME SHEET OR ADD "SHEET1! TO THE FRONT OF YOUR RANGES
IE SHEET1!\$A:\$B

Suggestion:
To see the syntax go to another sheet , select cell , click = button then select another sheet... you will see the syntax auto built for you

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•