0
72816

Using Dates within Excel Sumif

Using Dates within Excel Sumif

• AAT Qual/ACCA Student need license for bookkeeping

Can anyone help with the following:

I have a column of dates with numbers in the next column. I wish to add all of the numbers which have a date greater than my conditional date.

The formula follows the logic:
Sumif(Column1,>conditional date,Column2).
The conditional date and calculation are being performed wihtin another workbook if this makes a difference!

Formula works if I replace the conditional date with an actual number but that isn't where I need to be.

Any help is gratefully appreciated.

Lynne Alden

Replies

07th Jul 2004 13:25

SUMIF
Lynne

The following formula works great in one of my worksheets.

=SUMIF(A1:A3,">=01-JUL-04",B1:B3)

The cells in column A in this example are formatted to Date format.

Thanks (0)
By malong
07th Jul 2004 13:52

Use DSUM with appropriately placed criteria range
Try this - In BOOK 1 enter "date" into a1, "value" into b1. Fill a2:a6 with dates say 1/7/2004 - 5/7/2004; fill b2:b6 with numbers say 1 - 6 (a1:b6 has now become your database range). Open BOOK 2b. Enter "date" into a1; enter <4/7/2004 into a2 (this becomes the criteria range and can be embedded anywhere so long as the column header is the same as the database header) Now in say c2 (BOOK 2) enter the following formula:

=DSUM([Book1]Sheet1!\$A\$1:\$B\$6,2,A1:A2)

this gives you yr answer,and is flexible when further records are entered.

Thanks (0)
By 2031387
07th Jul 2004 13:35

Conditional date is not a constant
Thanks Mike but unfortunately my conditional date is not a constant but rather a row of dates across the top of a table. I need to set the formula up to be dependant upon this conditional date and then copy it across so that it looks at each separate date across the table header row.

Lynne

Thanks (0)
By lynamn
08th Jul 2004 17:05

Sumif second argument
Try replacing >conditional date with ">" & conditional date

e.g. =SUMIF( \$A\$3:\$A\$11, ">" & C1, \$B\$3:\$B\$11)

where
the record dates are in A3 to A11
the record values are in B3 to B11
the conditional date is entered in C1

It shouldn't make a difference that this date is in a different workbook.

Thanks (1)
07th Jul 2004 17:58

Several ways to crack a nut.
=SUM(Column2*(column1>BaseDateCellRef))

Enter as an array formula. Which is to say, hold down the control and shift keys and then hit the enter key. In the formula bar the formula should then be displayed in {curly brackets}

If BaseDateCellRef is a relative address then you should be able to copy the formula sideways. Anchor columns 1 and 2 with an absolute (\$) reference or by named range.

Thanks (0)
09th Jul 2004 13:03

One of my more satisfying creations was

{=MOD(11-MOD(SUM(VALUE(MID(UTR,ROW(INDIRECT("\$2:\$10")),1))*{6;7;8;9;10;5;4;3;2}),11),10)}

Where UTR is a reference containing a UTR.
If the value of the first digit in the UTR is not equal to the value returned by the above then the UTR is invalid.

Thanks (0)
08th Jul 2004 17:02

Arrays in Excel
Clint's answer highlights one of the most useful and underused features of Excel - arrays. When doing complex calculations on large amounts of data, they are usually very helpful.

EG weighted average of column C, with weighting in column D is
=SUM(C:C*D:D)/sum(D:D) entered as an array.

Clint's neat solution shows that the SUMIF function can easily be replicated by an array, but that the latter is much more flexible.

Anyone else have cool uses of arrays they want to share?

Thanks (0)
04th Dec 2012 05:18

today() function with sumif function

Good work Lynamn. Lynne can use today() function in cell C1. Thank you all.

Thanks (0)