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 •

October 11, 2009 at 06:21:31

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	
14		Total	113	

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

The formula in cell D14 is

2. Use an array formula. For this you don't need the fourth ('Match') column
The formula in Cell D14 is

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

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.


Report •

October 11, 2009 at 07:06:54
Close but not what I am looking for. My fault for not being clear.

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 •

October 11, 2009 at 07:09:25

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

In D14 enter

It's a regular formula, no need for the Ctrl+Shift+Enter!


Report •

Related Solutions

October 11, 2009 at 07:32:30

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:

Note the $ signs.
Then drag the formula to extend it to the other cells
D11 should be:

This is based on a solution DerbyDad03 gave to a previous question.


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 •

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

Report •

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

Report •

Ask Question