total of certain cells using 2 criterias

[Guest]

I want to do a sumif but using 2 criterias which appear in 2 separate columns
within the worksheet.

a yes 15000
a no 16000
a yes 20000
a no 30000
b yes 40000
b yes 50000

In the table above, I need to know the sum of column c if I choose a
criteria found in column a and column b together.
Can you help?
Thanks
Rita

 

[Bob Phillips]

=SUMPRODUCT(--(A1:A100="a"),--(B1:B100="yes"),C1:C100)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Nick Hodge]

Rita

If the data is in A1:C6, for example, the following will sum the data in C
where A="b" and B="yes"

=SUMPRODUCT(--(A1:A6="b"),--(B1:B6="yes"),(C1:C6))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Thank you boys and it worked a treat, but can you explain why you inserted
"--" in the formula? What is its function in the array?
Thanks

 

[Max]

.. can you explain why you inserted
"--" in the formula? What is its function in the array?

Try the 2* responses in this previous post:

http://tinyurl.com/64py9

*Bob Phillips' link to his page, and
Jason's example and explanation, with a nice touch on its evolution

See also JE McGimpsey's:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

 

[Ken Wright]

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

 

[Dana DeLouis]

You've got the best answers, but if you ever get stuck in the future with
something similar, here is another option...
Go to Tools | Add-Ins, and select "Conditional Sum Wizard." Click ok.
Then go to Tools | Conditional Sum..
This will walk you thru setting up your equation.
Excel's Conditional Sum wizard prefers to use SUM & IF as an array formula.
(Entered with Ctrl+Shift+Enter). The wizard will do this for you.

=SUM(IF(Col_A="a",IF(Col_B="yes",Col_C,0),0))

You can remove the ending 0's if you wish.
=SUM(IF(Col_A="a",IF(Col_B="yes",Col_C)))

It's best to have column headings, but if not, you can go back and edit your
equation and re-enter the formula (with Ctrl+Shift+Enter).
Again, just another option if you get stuck.

 

[Tushar Mehta]

It converts the boolean results of (A1:A6="b") into the numbers 0 or 1.
The same could be achieved with 0+ or 1* or even the N() function.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions