Sumif Issue

Iam trying to add up quanties in col B, C or D if the meet the criteria of being between two dates i specify(the dates are in col A). I defined the cols in excel with the names "Dates", "One" "Two", and "Three".

when using this fromula:
=SUMIF(Dates,"<="&DATE(2008,3,30),One)-SUMIF(Dates,"<"&DATE(2008,3,1),One) Everything works.

But lets say i didnt write "One" instaed the word One is in cell G1 and i use this formula:
=SUMIF(Dates,"<="&DATE(2008,3,30),$G$1)-SUMIF(Dates,"<"&DATE(2008,3,1),$G$1)

the eq. does not work anymore and i get a value of zero. does anyone know y? I need to do this bc if copy it across multiple rows i dont want to have to change the "One" on every col.

* i will have a lot of cols and they will not be right next to eachother
* attached reference sheet

You need to refer to G1 indirectly....

e.g. =SUMIF(Dates,"<="&DATE(2008,3,30),INDIRECT($G$1))-SUMIF(Dates,"<"&DATE(2008,3,1),INDIRECT($G$1))

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save