SUMIFS with an $ absolute function

[roc_ent]

Hi everyone, I have a bit of a problem with a sumifs and I hope you can help me get onto the right track. I have two sheets in one workbook, the first workbook has the formula like this =SUMPRODUCT(--(LEFT('512-X'!A6:A30,1)="1"),'512-X'!C6:C30) which is searching for a reference to "1" being January. Is there a way to make it so that I simply drag the formula so that I would be the same but the "1" would change to a "2" representing February, all the way to December. Here is a picture that may explain better. Thanks in advance.
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

 

[JoeMo]

Substitute: COLUMN(A1) for "1".

 

[roc_ent]

sorry Joe, I may not have explained it properly, or used the right formula. I've changed it to a regular sumif not ifs, so the formula goes like this =SUMIF('512-X'!A6:A30,"1",'512-X'!C6:C30) now I would like to simply drag it to the right keeping the A6:A30 but rather than looking for a "1" and adding the C6:C30 I would like it to change to a 2. I thought the absolute command was a $ but I've tried it and it gives me an error. So on the same row I simply want to change the reference 1 to 2 then to 3 ect all the way to Dec which will be 12. Hope this helps

 

[JoeMo]

Maybe:

=SUMIF('512-X'!$A$6:$A$30,Column(A1))

 

[roc_ent]

This is not going very good for me, if I try to put the $ sign anywhere at all I get an error message. I've copied the formulas in my workbook that I've done by hand one by one and pasted them in word so you can see what I have. with the picture, you will see that all I want to do is change the criteria from 1 all the way to 12 what I want is to be able to click and drag all the way to M28 without having to manually write the formula again. Here is the url to the pic: Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting thank you so much

 

[MarcelBeug]

How about

=SUMPRODUCT(--(LEFT('512-X'!A6:A30,1)=COLUMNS($B1:B1)&""),'512-X'!C6:C30)

By the way: this will never work as you need 2 digits for October-December...

 

[roc_ent]

thanks guys for your help, however it did not seem to work. What I did was name defined each column then dragged the formula from my column a to m and had to go in and only change the criteria for all 12 months but at least it saved me a lot of typing. Again thanks