SUMIF question

[Jim]

Hi,

I have a workbook with column A formatted as dates, and column B containing
numbers. I would like to know how to get excel to tell me how much the sum
of the numbers would be for a given time frame. So, for instance, if I
wanted to know the sum of the numbers in column B that have year 2006 in
column A, etc. There must be a way to do this, but I'm stumped.
Also, can the criteria in the date be narrowed to give me a sum of just,
say, a given month within a given year?

Thanks in advance,

Jim

 

[Guest]

Try this:

With
Dates in A1:A30
Amounts in B1:B30

Here are 2 variations of the same formula that return the sum of amounts
from 2006:
C1: =SUMPRODUCT((YEAR(A1:A30)=2006)*B1:B30)
or
C1: =SUMPRODUCT(--(YEAR(A1:A30)=2006),B1:B30)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Jim]

Ron,

Many thanks - they both work fine. now...can you explain why?? <g>

thanks again!

Jim

 

[Guest]

can you explain why??

Sure....

One of the typical uses of SUMPRODUCT is to multiply the corresponding cells
of 2 same-size ranges and aggregate the products.
Example:
=SUMPRODUCT(A1:A30,B1:B30)
would multiply A1 X B1, then A2 X B2, etc
then it would add up all of the results

In your case, we're tweaking one of those ranges (A1:A30) to substitute the
cell values with a boolean (TRUE/FALSE) expression. When TRUE/FALSE values
are impacted by mathematical operators they convert to 1 and 0, respectively.
The math operator we're using is the dbl-minus-sign ( -- ). The
dbl-minus-sign is just a standard convention that we use to indicate that we
are converting a boolean (or numeric text) value to a number.

In this formula
C1: =SUMPRODUCT(--(YEAR(A1:A30)=2006),B1:B30)
This part: --(YEAR(A1:A30)=2006)
calculates the year for each date in A1:A30 and tests if it equals 2006.
TRUE values are converted to 1's,.
FALSE values are converted to 0's.

The end result is that each 2006 date becomes a 1 and is multiplied times
the corresponding amount in Col_B. The Non-2006 dates become 0's, so their
corrsponding values become 0's ( 0 x value = 0 )

The SUMPRODUCT function adds up the products (values from 2006 and zeros for
Non-2006 values)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Jim]

Ron, that makes perfect sense...thank you for the simple, logical and
eloquent explanation. My only remaining question would be that if the second
example works so well (as it does), is there a particular reason you added
it as an alternative to C1: =SUMPRODUCT((YEAR(A1:A30)=2006)*B1:B30) ? Does
the lack of the -- imply that it wouldn't work under certain circujmstances?

Thanks again for all your help. I am really starting to get into more
functions of excel as time goes by, and I'm finding this a very helpful
place. I also bought a "for dummies" book, Excel all in one desk reference
that I intend to read very soon. Are there any basic publications or sites
you'd suggest?

Jim