drag a SumIf formula

[roc_ent]

Hi all, Can anyone help me out as I'm doing this one by one and there must be a faster way that I'm not aware of.

I have a workbook of 28 pages. One tab is referenced by the month as in Jan-Wrk and its partner tab is Jan-Financial.

In the work book, I created a general ledger with a column named account number. So the entry is made and in this C1 is the designated account number. Example is 1 for donation, 2 is for fundraising and so on.

In my financial tab, I'm using this formula =SUMIF('Apr Led'!$C$5:$C$104,1,'Apr Led'!$D$5:$D$104) Now what I would like to do is be able to drag this formula down so that everything stays the same EXCEPT the account number which in this case is 1.

What I'm doing now is copy and paste this one formula, and them manually changing every account number one at a time and for 40 account x 12 months, its a lot of key strokes.

Does anyone have a solution for me. Thanking each in advance.

 

[mikerickson]

Does
=SUMIF('Apr Led'!$C$5:$C$104, ROW(A1) ,'Apr Led'!$D$5:$D$104)

do what you want?

 

[roc_ent]

Thanks Mike, but unfortunately no it did not work. what is going on as well is in the LED the column with the account number is in C5 so I changed the A1 to C5 and it still did not work. Once I drag it down, it's giving me amounts that have not yet been posted.

 

[tyija1995]

If you changed ROW(A1) to ROW(C5) that is the equivalent of changing 1 to 5, as ROW(C5) essentially looks up the account number 5 in this case. Dragging down will increment the account number by 1 each time.

 

[roc_ent]

Thanks T, I'm not getting the proper return. This is most likely due to a high pay level (lol) than I'm at so I guess I will just keep going one at a time. Thanks anyways

 

[roc_ent]

Maybe I did not explain my situation correctly. here is a formula that is working at the present time
=SUMIF('May Led'!$C$5:$C$104, 3,'May Led'!$D$5:$D$104) now its reporting the proper amount for account #3

What I would like to do is drag this formula down so that the account changes to 4, 5, and so on
When I drag it now it does not do that

 

[tyija1995]

To augment like that the solution provided initially should work, when you use ROW(A3) instead of 3 and drag down, you get ROW(A4), ROW(A5),...,ROW(A104) - this as a value is just the number inside the brackets, so it is doing as you want.

Example: if you start writing the formula in row 5 and want the first account to be number 1 then:
=SUMIF('May Led'!$C$5:$C$104, ROW(A1),'May Led'!$D$5:$D$104)

Ignore that ROW(A1) Points at column A - it could say ROW(XFD1) and it returns the same value as it only concerns the row.

 

[roc_ent]

You are simply "FANTASTIC". Thank you very much for the time you will save me and my index finger also thanks you as it was getting shorter from keying in all those changes manually. lol