Excel sum one column based on criteria in 2 columns with different data

I'm not sure whether you can use SUMIF with 2 criteria. Here's how to you do it with one. Type "SUMIF(x,y,z)". where x is the range of cells, the criteria is checked upon. y is the criteria, z is the range of cells to sum. suppose the name column goes H1:H15 and the numeric Value goes I1:I15 you should type SUMIF(H1:H15,"GH",I1:I15).

I havethe exact same need and I would like to add to this question, How would you make the date  be, a Greater that date and the reference date is contained in a cell (A1), below does not work

Sumif(A2:A50,>A1,B2:B50)

A2:A50 contains the dates

B2:b50 contains $'s

A1 contains the date I want all data summed beyond

Let's say your dates are in column A from cell 2 to 5 (range = A2:A5), your names are in column B from cell 2 to 5 (range = B2:B5), your numeric values are in column C from cell 2 to 5 (range = C2:C5).

Try this as your formula:

=SUMIFS(A2:A5,"=Oct-07",B2:B5,"=GH",C2:C5)

What this does is it checks each cell in column A against the criteria =Oct-07 (NOTE - you'll may have to tweak the criteria to fit your date format) and also checks column B values for the criteria =GH. Then, it sums those cells in column C that fit both criteria.

If your criteria comparison is against a value in another cell -- say for example you have the date set in cell E1, then you can use the cell in the criteria thus:

=SUMIFS(A2:A5,"="&E1,B2:B5,"=GH",C2:C5)

The & identifies the E1 as a range versus literal text. This allows you to avoid hard-coding the criteria in the formula which has the benefit of allowing you to change the value in the cell E1 and see what it does to your calculation.

HTH

Jim,

For handling the > try this:

SUMIF(A2:A50,">"&A1,B2:B50)

As before, the & identifies the A1 as a cell versus the literal text of the criteria.

really very interesting post and comments too

Canon XS

I do not see a Syntax for  =SUMIFS.  Is this a typo/  I also am hard pressed to find a formula to add values in columns based on 2 criteria in , each in different columns.  Has anyone posted a solution?

SUMIFS only exists in Excel 2007.
A similar way to do this in earlier version is the following formula:

=SUMPRODUCT(--($B10:$B92>="Expense"),C10:C92)

This formula will sum all cells in Column C provided the corresponding cell in Column B is "Expense".

What does the -- mean at the front?