Sumif withVlookup or something different.

[Mooresmi]

I need to sum a total of column of figures dependent on a entered date. Example is
Col.A (range)= list of dates (everyday for a year)
Col.B = (sum_range) List of values to be counted
Criteria is a date in a static cell.

What I need is to sum the values as long as the date is less than or equal to the criteria date.

My feeble attempt was =sumif(A:A,"<=c1",B:B) but of course this does not work; the sumif function doesn't seem to be able to determine if the cell value of C1 is <=, the result is always zero.

 

[Mark W.]

Is there a data type mismatch between your column A:A values and C1? Do you intend to Sum or Count the B:B values? The body of your posting said "count" but your subject line implied "sum".
This message was edited by Mark W. on 2002-10-16 18:14

 

[Mooresmi]

Please replace the word "counted" for "summed". Thanks, Gordon

 

[maxflia10]

Try this

=SUMIF(A1:A4,"<="&MONTH(C1)&"-"&YEAR(C1),B1:B4)

Where C1 is the cell that houses the date. If it works credit to Aladin, if not my fault

Here's the thread

http://216.92.17.166/board/viewtopic.php?topic=25133&forum=2
This message was edited by maxflia10 on 2002-10-16 22:21

 

[Mooresmi]

[No message]

 

[Mooresmi]

Credit to Aladin
Thanks Aladin.
This is what I ended up with

=SUMIF('I1'!B:B,"<="&DAY(C$1)&"-"&MONTH(C$1)&"-"&YEAR(C$1),'I1'!I:J) +SUMIF('I1'!B:B,"<="&DAY(C$1)&"-"&MONTH(C$1)&"-"&YEAR(C$1),'I1'!AG:AH) +SUMIF('I1'!B:B,"<="&DAY(C$1)&"-"&MONTH(C$1)&"-"&YEAR(C$1),'I1'!BE:BF)

$ sign for filldown formula and just change with Replace function, the sheet name.
Thanks again.

 

[maxflia10]

Gee, at least I thought I would get some credit for finding it!