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

Report •


#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') 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


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 C
1 Bob Cans 6
2 Jane Bottles 2
3 Bill Cans 3
4 Bob Bottles 4
5 Bob Cans 9

I 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 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.


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 •


Ask Question