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?

=INDEX(Sheet1!$C$3:$C$1000,MATCH(Sheet2!A3,Sheet1!$G$3:$G$1000,0))*0.03

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

=IFERROR(INDEX(Sheet1!$C$3:$C$1000,MATCH(Sheet2!A3,Sheet1!$G$3:$G$1000,0))*0.03,"")

=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?