sumif formula automatic date change criteria

Hi sonor_nut,

You could try adding +ROW(A1) to the date? This acts as a counter that will increment every cell it goes down (since in the next row it will count ROW(A2) which = 2). This should make the date increment?

Hope this helps.

Originally Posted by sonor_nut

Thank you for the reply. My understanding of excel is very limited at the moment. Do you mean something like this: =SUMIF('sheet1'!G:G,"=01/09/2012+ROW(A1)",'sheet1'!C:C)*0.03?

I have attached an example of the sheet I have, hopefully it will clear things up a bit. I want in sheet 2 column C to have the sumif formula work out what work is going to be delivered on 1 sept then return the value of the corresponding number in column c of sheet 1 * 0.03. I then want to fill the formula down for the other dates. I know the formula is correct because I have manually inputted it into the cells below and changed the date myself to make sure it works. My question is can I not have it automatically change the date so when I fill it from the row containing the date 01/09/2012 to the one below the date in the criteria of the SUMIF formula will change accordingly? Hope that makes sense.

Does this work for you?

Please Login or Register  to view this content.

Paste into C3 and copy down. This looks at the value in A3 (the date - 1st Sept), finds it within column G of sheet 1 (forecast work in date), and gives the relative value from column C of sheet (the cost on that day) and then multiplies it by 0.03.

Hope this helps.

Originally Posted by sonor_nut

It seems to be starting to work thank you however if it doesn't find anything it returns this: #N/A, is there a way I can get it to return 0 or just leave it blank?

Yep

Please Login or Register  to view this content.

=IFERROR lets you specify a value for anything where any errors occur. In this case, I put "", which is a blank but you can replace this with zero if you wish.

You have had list of dates of months in column A already, why dont you use this:
=SUMIF(Sheet1!G:G,A3,Sheet1!C:C)*0.17
Fill down
or I missed sth?