There might be an easier way, but one solution is to use arrays. Suppose,
for example, you have a table of business expenses for the current year, with
the month in column A, the type of expense ("Meals", "Hotel", "Airfare",
etc.) in column B and the amount in column C. Suppose there are values in
rows 10 through 20.

In order to find, say, the total amount of all meal expenses incurred in the
month of March, you could enter the following formula (in the cell where you
would have used SUMIF, if you only had one criterion):

= SUM( IF( A10:A20 < "March", 0, IF( B10:B20 < "Meals", 0, C10:C20 ) ) )

In order to make Excel treat the ranges in this formula as arrays, after
typing the formula, you must press Ctrl+Shift+Enter, rather than just the
Enter key. (If you have already pressed Enter -- in which case you probably
got a #VALUE! error -- just go back into 'edit' mode using the F2 key, then
press Ctrl+Shift+Enter.)

You will know that you have entered the equation correctly because Excel
will put brace brackets, i.e., '{' and '}', around the equation when it is
displayed in the formula bar (but they disappear when you edit the formula.)

By using arrays, Excel will evaluate each element of the array (or
corresponding elements of different arrays) individually, and put the results
in the appropriate place in the formula. Therefore, it will substitute A10,
B10 and C10 into the equation and the result of the IF statement will become
the first argument of the SUM function. Then it will substitute A11, B11 and
C11 and the result of the IF function will become the second argument in the
SUM function, and so on. If the expenses in rows 12, 17, 18 and 20 are the
only ones to match your criteria, the function will effectively reduce to the
following, once array expansion is complete:

= SUM( 0, 0, C12, 0, 0, 0, 0, C17, C18, 0, C20 )

Note that it is important to use the nested IF statements, as shown, rather
than using the AND function. The reason is because the IF function takes a
well-defined number of arguments, whereas AND takes a list of unknown length,
so the array expansion doesn't happen the way you might expect. By way of
example, consider the result if you enter the equation as follows:

= SUM( IF( AND( A10:A20 = "March", B10:B20 = "Meals" ), C10:C20, 0 ) )

Because AND can take many arguments, Excel expands the arrays inside the AND
function, so that part of the equation reduces to:

AND( A10="March", A11="March", A12="March", ..., A20="March", B10="Meals",
B11="Meals", B12="Meals", ..., B20="Meals" )

Since not all your expenses were meals and not all of them were in March (or
you wouldn't be trying to do this at all!) the AND function will ALWAYS
return FALSE, and the SUM function will always return zero.

I hope this is helpful!
Hugh John


"Petros" wrote:

Hi!
I would liek to use sumif but use criteria from 2 columns instead of one. i
tried to use the conditional sum wizard in the add-ins but when I change
something it does not seem to work. Any ideas?
Thank you
Petros