SumIF within date range over multiple months/years

[Davenmarc]

Good Day All. I am hoping for some help with regards to SumIF. I have multiple conditions which are working except the sum column between a specific date range. The Formula works if the date range is in the same month but as soon as i expand it to another month the formula goes crazy and only uses the days of the month and ignores the month.

My formula is as follows: =SUMIFS(K5:K35;A5:A35;">="&Q9;A5:A35;"<="&Q10)

If i use the dates 01/01/2007-31/01/2007. The sum values are correct.
If i use dates 01/01/2007-05/02/2007. The sum values ignore the entire range and only focus on numbers between 01-05 of both months.

Any help would be appreciated.

Also not sure how to upload a sample copy if i havent explained this too well.

[Special-K]

A couple of thyings to check first.
Ensure A5:A35 are actually dates and not text. In a blank column on row 5 enter ISNUMBER(A5) and copy down as far as the 35th row. The answer should say TRUE. If it says FALSE then the value in column A is text and you need to change that value to a proper date.
Do the same ISNUMBER() for cells Q9 Q10
If that failes then upload a copy of the file.

To upload a sample copy

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[Davenmarc]

Hey Special K

Done that and says uploaded. I have looked. It shows none of the dates as numbers. I have tried to change the cell format to number. Still gives the same error back.As i said ive attempted to upload a copy hope it works.

Thanks for the help

[Special-K]

in U1
=A1+0
copy down to U31

Select U1:U31
Copy
Use "Paste Values" overwriting column A

Column A are now numbers and the result in Q8 has changed from 0.7 to 6.7 which is the total of K1:K8 which are also the rows relating to the date range 17/01/2007 to 31/01/2007 as specified in Q5 and Q6

[Davenmarc]

Hi Special K

When inserting =a1+0, it gives a #value! error in U1.

Not sure if im being stupid while doing it and missing something obvious?

[Special-K]

Use the file you posted in post #3
Select U1
type =A1+0
Result is 17/01/2007 as before but it's now a number as you've added 0 to it.

Works ok for me.

[Davenmarc]

Hi K

I Have uploaded another copy of the file with the A1+0.

Sorry if im missing somthing obvious.

[Special-K]

U1 is based on A1 and is displaying #VALUE.

A1 contains #VALUE for some reason ???
It did say 07/01/2007
Once you fix the error in A1, U1 will correct itself (hopefully).

[Davenmarc]

Hey K

I fixed the dates side. But U1 still displays the same error. for what reason i do not know. I have attached it again with the correct dates.

Regards

[Special-K]

Retype the formula and copy down and follow instructions as before "Paste Values" etc