SUMIF function

[Igor]

Hi

Is it possible to use AND in SUMIF function. I have a simple table with
three columns. The first column contains dates in chronological order. I
want to use SUMIF function to sum values from the second column but only for
a specific period of time (ie one year).
For example: =SUMIF(A2:A25;"<38353";B2:B25) sums all values before year
2005, so 2004, 2003, etc.. How should the function look like to sum values
only for one year, 2004 for instance.

Kind regards

 

[Dave Peterson]

First, I'd use:
=SUMIF(A2:A25;"<"&date(2005;1;1);B2:B25)
That number 38353 may not always represent Jan 1, 2005. It'll depend on how the
base date is set (1900 or 1904).

=sumproduct(--(text(a2:a25;"yyyy")="2004");b2:b25)

(watch out. I _think_ I got all my commas changed to semicolons!)

 

[Dave Peterson]

ps.

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[David Biddulph]

=SUMIF(A2:A25;"<38353";B2:B25)-SUMIF(A2:A25;"<37987";B2:B25)
or
=SUMPRODUCT((A2:A25<38353)*(A2:A25>=37987)*(B2:B25))