I want to sum one column where it meet 2 criteria in 2 different columns...Lookup a value in one column, a second value in a different column...where both match sum up values in a third column.

Hi, If I understand you correctly, you want to sum all the values in a third column, when the values in columns 1 and 2 on that row are the same.

There are several ways to do this and here are two of them:

1. Use a fourth column to compare columns 1 and 2 and then use the SUMIF() function to sum the values on the rows where there is a match

B C D E 2 Data 1 Data 2 Values Match 3 A R 75 4 C T 45 5 G H 29 6 Y D 59 7 R R 95 X 8 W B 33 9 F F 18 X 10 G H 93 11 Y E 33 12 C W 10 13 14 Total 113The formula in cell E3 is

=IF(B3=C3,"X","")

then drag the formula down to the last row of data, row 12 in this example.The formula in cell D14 is

=SUMIF(E3:E12,"X",D3:D12)2. Use an array formula. For this you don't need the fourth ('Match') column

The formula in Cell D14 is

=SUM(IF($B$3:$B$12=$C$3:$C$12,$D$3:$D$12,0))To make this work as an array formula, after entering the formula, put your cursor in the formula bar somewhere inside the formula itself and then hit Control + Shift + Enter (all three keys together).

If you get this correct your formula will be surrounded by curly brackets

{=SUM(IF($B$3:$B$12=$C$3:$C$12,$D$3:$D$12,0))}Entering curly brackets from the keyboard does not work and hitting the Ctrl+Shift+Enter combination when the cursor is not in the formula does not work.

Regards

Close but not what I am looking for. My fault for not being clear. A B C

1 Bob Cans 6

2 Jane Bottles 2

3 Bill Cans 3

4 Bob Bottles 4

5 Bob Cans 9I want to look in column A for a name, in B for a product, then Sum the numbers in C where both criteria match. So looking for Bob and Cans my total would be 15.

Thank you for your response and any follow up you can provide.

Hi, In a previous post DerbyDad03 showed how to use the SUMPRODUCT() function.

In D14 enter

=SUMPRODUCT(($B$3:$B$12=$C$3:$C$12)*($D$3:$D$12))It's a regular formula, no need for the Ctrl+Shift+Enter!

Regards

Hi, My last posting crossed yours, so please ignore it as it doesn't address your question.

Hopefully this will.

If the data is like this:

B C D 2 Bob Cans 6 3 Jane Bottles 2 4 Bill Cans 3 5 Bob Bottles 4 6 Bob Cans 9Then create a results matrix:

8 Cans Bottles 9 Bob 15 4 10 Jane 0 2 11 Bill 3 0The formula in C9 is:

=SUMPRODUCT(($B$2:$B$6=$B9)*($C$2:$C$6=C$8)*($D$2:$D$6))Note the $ signs.

Then drag the formula to extend it to the other cells

D11 should be:

=SUMPRODUCT(($B$2:$B$6=$B11)*($C$2:$C$6=D$8)*($D$2:$D$6))This is based on a solution DerbyDad03 gave to a previous question.

Regards

PS if posting again, use the pre /pre tags (inside angle brackets)

for table data - they pretty much keep the formatting in columns etc.

A set of tags can be inserted using the icon above the message box.

Thank you very much. Works like a charm.

You're welcome

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History