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

Consider SUMPRODUCT instead:

Here is some data:

1 happy
2 happy
3 happy
4 happy
5 happy
6 happy
7 happy
8 happy
9 happy
10 happy
11 happy
12 happy
13 happy
14 happy
15 sad
16 sad
17 sad
18 sad
19 sad
20 sad

and we want the sum of column A if:
1. the value in A is greater than 10
2. the value in column B is happy

=SUMPRODUCT((A1:A2010)*(B1:B20="happy"),A1:A20)
which will return 50


For a really good discussion, see:

http://www.cpearson.com/newsletter/c...2007_08_13.htm


--
Gary''s Student - gsnu200774


"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

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

Hi, Petros

I think Gary''s response is definitely better than mine for what you are
trying to do. Who knew that you could multiply truth values??

On the other hand, my solution can be used in place of COUNTIF as
well...just substitute a 1 for the range C10:C20. The rest of the equation
remains unchanged (i.e., you still use SUM, not COUNT.)

In any case, I hope you got what you need!

Regards,
Hugh John



"Gary''s Student" wrote:

Consider SUMPRODUCT instead:

Here is some data:

1 happy
2 happy
3 happy
4 happy
5 happy
6 happy
7 happy
8 happy
9 happy
10 happy
11 happy
12 happy
13 happy
14 happy
15 sad
16 sad
17 sad
18 sad
19 sad
20 sad

and we want the sum of column A if:
1. the value in A is greater than 10
2. the value in column B is happy

=SUMPRODUCT((A1:A2010)*(B1:B20="happy"),A1:A20)
which will return 50


For a really good discussion, see:

http://www.cpearson.com/newsletter/c...2007_08_13.htm


--
Gary''s Student - gsnu200774


"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