which formula to use
 

I have data in 3 different columns and want to use criteria from column A and
B and to give me the result of column C in a different worksheet.

e.g
A B C
Finance Debbie 10
IT Bill 20
Finance Debbie 15
Finance Angie 30

I want to gather the total amount (C) of all Finance (A) Debbie (B) to a
different worksheet.

What formula do I use?

which formula to use
 

--When you have multiple criteria use SUMPRODUCT()

=SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2), C1:C10)

In Sheet2 cell A1 = Finance and B1 = Debbie..In Sheet2 try the below formula
=SUMPRODUCT(--(Sheet1!$A$1:$A$100=A1),
--(Sheet1!$B$1:$B$100=B1),Sheet1!$C$1:$C$100)


If you are using Excel 2007 you can use SUMIFS() to acheive the same result

=SUMIFS(C1:C10,A1:A10,criteria1,B1:B10,criteria2)

'or with cells F1 and F2 holding the criteria
=SUMIFS(C1:C10,A1:A10,F1,B1:B10,F2)

If this post helps click Yes
---------------
Jacob Skaria


"DINAKI" wrote:

I have data in 3 different columns and want to use criteria from column A and
B and to give me the result of column C in a different worksheet.

e.g
A B C
Finance Debbie 10
IT Bill 20
Finance Debbie 15
Finance Angie 30

I want to gather the total amount (C) of all Finance (A) Debbie (B) to a
different worksheet.

What formula do I use?

which formula to use
 

Use sumproduct
=sumproduct((a2:a22="finance")*(b2:b22="debbie")*c 2:c22)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DINAKI" wrote in message
...
I have data in 3 different columns and want to use criteria from column A
and
B and to give me the result of column C in a different worksheet.

e.g
A B C
Finance Debbie 10
IT Bill 20
Finance Debbie 15
Finance Angie 30

I want to gather the total amount (C) of all Finance (A) Debbie (B) to a
different worksheet.

What formula do I use?

which formula to use
 

This sounds like a perfect situation to learn about pivottables--especially if
you need all the data for all departments and names.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

DINAKI wrote:

I have data in 3 different columns and want to use criteria from column A and
B and to give me the result of column C in a different worksheet.

e.g
A B C
Finance Debbie 10
IT Bill 20
Finance Debbie 15
Finance Angie 30

I want to gather the total amount (C) of all Finance (A) Debbie (B) to a
different worksheet.

What formula do I use?

--

Dave Peterson

which formula to use
 

Or you might try a pivot table, which will do that sort of summation
automatically if you arrange your fields correctly.

Select all of your data. Then select the menu "Data/Pivot Table and Pivot
Chart Report...". Then go through the steps to create the pivot table,
arranging your data as you desire.

HTH,

Eric

"Jacob Skaria" wrote:

--When you have multiple criteria use SUMPRODUCT()

=SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2), C1:C10)

In Sheet2 cell A1 = Finance and B1 = Debbie..In Sheet2 try the below formula
=SUMPRODUCT(--(Sheet1!$A$1:$A$100=A1),
--(Sheet1!$B$1:$B$100=B1),Sheet1!$C$1:$C$100)


If you are using Excel 2007 you can use SUMIFS() to acheive the same result

=SUMIFS(C1:C10,A1:A10,criteria1,B1:B10,criteria2)

'or with cells F1 and F2 holding the criteria
=SUMIFS(C1:C10,A1:A10,F1,B1:B10,F2)

If this post helps click Yes
---------------
Jacob Skaria


"DINAKI" wrote:

I have data in 3 different columns and want to use criteria from column A and
B and to give me the result of column C in a different worksheet.

e.g
A B C
Finance Debbie 10
IT Bill 20
Finance Debbie 15
Finance Angie 30

I want to gather the total amount (C) of all Finance (A) Debbie (B) to a
different worksheet.

What formula do I use?