Help writing SumIF with Multiple Criteria 1

[TAngel]

I need a help writing a formula that sums column C1:C3000 if Column A = Dental Care and in the same row Column B = Spouse. How do I do that?
Thanks,
Teresa

 

[xlbo]

In 2010 I think there is a new formula for this but outsode of that, you can use SMPRODUCT()

=SUMPRODUCT((UPPER($A$1:$A$3000)="DENTAL CARE")*(UPPER($B$1:$B$3000)="SPOUSE")*($C$1:$C$3000))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TAngel]

Thanks Geoff, I haven't used SumProduct before. This totally worked for me. Thank you
Teresa

 

[AnotherHiggins]

The new function, which became available in Excel 2007, is SUMIFS. It is like SumIf, but with multiple Conditions available.

It actually has a couple of advantages over SumProduct:
[ul][li]It can accept whole column references (No more A2:A65536)[/li]
[li]It doesn't break on mixed data types, like including a text header in a column of numbers[/li][/ul]

In your case, it would look like this:
[tab][COLOR=blue white]=SumIfs(C:C, A:A, "DENTAL CARE", B:B, "SPOUSE")[/color]
(Note: It is case-independent)

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

Thanks John - knew it existed but my usage of 2007/2010 is limited at the mo

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fishymetrics]

There is a Conditional Sum Wizard in Excel under the Tools menu (You may have to download it from MS). You can have multiple conditions.It steps you thru the process and creates the formula for you.
This is what it created.

{=SUM(IF($A$2:$A$4="dental care",IF($B$2:$B$4="spouse",$C$2:$C$4,0),0))}

Notice the brackets outside that indicate an array.
FYI You can use CTRL+SHIFT+ENTER to create arrays