sumif formula not working

[johngio]

hi all,

another possibly simple problem:

My formula has evolved to this:

=SUMIF('Target 04'!B23:M23, "<"&DATE(YEAR(F$8),MONTH(F$8),DAY(1)), 'Target 04'!B56:M56)

the info from B23:M23 =
Jan-04 Feb-04 Mar-04 Apr-04 May-04 Jun-04 Jul-04 Aug-04 Sep-04 Oct-04 Nov-04 Dec-04

the info from b56:m56 =

40.6 41.2 42.8 40.6 42.8 42.9 42.8 42.8 42.9 42.8 42.9 42.8


however, when i set the date in f$8 to 1/11/04, i get an answer of 25. In fact, whatever i set the date to, i get answers of between 9 to 25.

Any ideas what I'm doing wrong ?

Thanks

 

[KleptoOne]

The logic in you formula states that you are adding all the values that fall before the first day of the month that is inputed. So 1/11/04 would be changed to 1/1/04 and should return a value of 0 with your sumif as is. I checked the logic in you statement and it works for me. Make sure your ranges are correct in reference to your data table. Maybe you can give a couple of examples of what someone would input and what they should get in return.

 

[Yogi Anand]

Hi johngio:

I am not quite clear on how you are comparing the Date in your SUMIF formulation, however please look at the following ...

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
7			Year	Month
8			2004	11	Nov-04	422.2
9			2004	7	Jul-04	250.9
10			2004	3	Mar-04	81.8
11
12
13
14
15
16	40.6	41.2	42.8	40.6	42.8	42.9	42.8	42.8	42.9	42.8	42.9	42.8
17
18
19
20
21
22
23	Jan-04	Feb-04	Mar-04	Apr-04	May-04	Jun-04	Jul-04	Aug-04	Sep-04	Oct-04	Nov-04	Dec-04
24
Target 04

I hope this helps!