MrExcel Publishing
Your One Stop for Excel Tips & Solutions

sumif with 2 criteria


Posted by Ric Blakeney on November 17, 2000 8:08 AM

I have a spreadsheet with 3 columns(A1:A100, B1:B100, C1:C100). In A101 and B101 I have place criteria that I want met. I need a formula that will add up the values in column C that meet both the A and B criteria. I can get a formula to tell me how many meet both criteria, but not to add them up. Somebody please help me!

Ric Blakeney


Posted by Tim Francis-Wright on November 17, 2000 10:57 AM

Check out Mr. Excel's sometime tip-of-the-week:
http://www.mrexcel.com/tip031.shtml

HTH

Posted by Aladin Akyurek on November 17, 2000 2:40 PM

I'm not sure I'm solving the right problem, but here we go.
Put the following formula in D1 and copy down until D101:

=IF(AND(C1>=crit1,C1<=crit2),C1,"")

After copying, you can sum the values with

=SUM(D1:D100)

in D101.

I don't know what condition you have in mind, so I made up one. Crit1 and Crit2 are the names of the cell A101 and B101.

As you, this solution needs an additional column. Tim's suggestion (below) is probably better.

Aladin