# excel formula

Microsoft Excel 2003 (full product)
October 11, 2009 at 05:27:00
Specs: Windows Vista
 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.

See More: excel formula

#1
October 11, 2009 at 06:21:31
 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 113 ```The 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') columnThe 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

Report •

#2
October 11, 2009 at 07:06:54
 Close but not what I am looking for. My fault for not being clear. A B C1 Bob Cans 62 Jane Bottles 23 Bill Cans 34 Bob Bottles 45 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.

Report •

#3
October 11, 2009 at 07:09:25
 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

Report •

Related Solutions

#4
October 11, 2009 at 07:32:30
 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 9 ```Then create a results matrix:```8 Cans Bottles 9 Bob 15 4 10 Jane 0 2 11 Bill 3 0 ```The 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 cellsD11 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.RegardsPS 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.

Report •

#5
October 11, 2009 at 19:29:22
 Thank you very much. Works like a charm.

Report •

#6
October 12, 2009 at 05:23:59
 You're welcome

Report •