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
Related resources
Replies (8)
Please login or register to join the discussion.
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.
Perhaps you could provide a bit more info. about the layout of your worksheets if the above does not work.
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.
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
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.
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.
Cool array, Adam?
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.
Sad, or what?
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?
today() function with sumif function
Good work Lynamn. Lynne can use today() function in cell C1. Thank you all.