Sumif function with multiple criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bobito - 26 Nov 2005 21:20 GMT

I am trying to "sum" a column using "sumif" ... but need to use 2 criteria.
I have tried the Sumif(And(< cond 1 >,> cond 2 >)(ans) but this does not work.
Has anybody any idea how write the correct statement

Reply to this Message

Bernard Liengme - 26 Nov 2005 21:33 GMT

SUMIF works only with 1 condition; for more you need SUMPRODUCT.
You have given us little to work with so here is a general reference
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Come back if you have more questions
best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

>I am trying to "sum" a column using "sumif" ... but need to use 2 criteria.
> I have tried the Sumif(And(< cond 1 >,> cond 2 >)(ans) but this does not
> work.
> Has anybody any idea how write the correct statement

Reply to this Message

Bobito - 28 Nov 2005 22:16 GMT

The SUMPRODUCT function will work within my spreadsheet, however, it is
limited to the fact you have to specify row numbers.
With the SUMIF function you can specify as an example ... SUMIF(A:A etc ...
ie- you do not have to specify over which rows you want the sum function to
work.
I tried this way with SUMPRODUCT but the only answer that was returned was
... #NUM!
Is it possible to use the row = A:A statement instead of specifying row
numbers?

> SUMIF works only with 1 condition; for more you need SUMPRODUCT.
> You have given us little to work with so here is a general reference
[quoted text clipped - 5 lines]
> > work.
> > Has anybody any idea how write the correct statement

Reply to this Message

Peo Sjoblom - 28 Nov 2005 22:48 GMT

If you use sumproduct you can't use A:A, you need to specify the rows

Signature

Regards,

Peo Sjoblom

> The SUMPRODUCT function will work within my spreadsheet, however, it is
> limited to the fact you have to specify row numbers.
[quoted text clipped - 15 lines]
> > > work.
> > > Has anybody any idea how write the correct statement

Reply to this Message

windsurferLA - 29 Nov 2005 16:47 GMT

I don't know enough about the your spreadsheet to claim it would be a
solution, but check out the User Defined Function (UDF) suggested by
Bernard Liengme in an earlier post. It would seem that you could modify
it to solve your problem.

WindsurferLA

> The SUMPRODUCT function will work within my spreadsheet, however, it is
> limited to the fact you have to specify row numbers.
[quoted text clipped - 16 lines]
>>>work.
>>>Has anybody any idea how write the correct statement

Reply to this Message