SUMIF function
 

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

SUMIF function
 

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!)





Igor wrote:

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

SUMIF function
 

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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



Igor wrote:

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

SUMIF function
 

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

"Igor" wrote in message
...
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