SUMIF Help

Posted by Fred Henry on March 15, 2001 10:43 AM

Help! 2 columns - column 1 contains dates (1/31/98, 2/28/98, 3/31/98, etc.) 2nd column is a sales total for the month in column 1. How to total all the sales figures for a given year, say 1998?

I suspect the answer is the SUMIF function, but I cannot get the =1998 criteria figured out.

Thanks in advance.

Posted by Dave on March 15, 2001 11:04 AM

Hi Fred

You could use the DSUM to do this or an array formula:
=SUM(IF(YEAR(A2:A12)=1998,B2:B12))

You must enter an array by push Ctrl+Shift+Enter.

There are a few more rules on arrays that you may be intrested in. If you follow my link and click "Array Formulas" you will see some examples.

Dave

OzGrid Business Applications

Posted by Aladin Akyurek on March 15, 2001 11:04 AM

Array-enter (hit CONTROL+SHIFT+ENTER at the same to enter) the following formula

=SUM((YEAR(A1:A10)=1998)*(B1:B10))

where A1:A10 contains date values and B1:B10 the values to sum.

You may even put the criterion year in a separate cell, say, C1 and replace 1998 by C1 in the above formula.

Aladin