# Solved Excel Problem (referring to cell contents in function)

Microsoft Office excel 2007 home & stude...
January 12, 2012 at 19:26:25
Specs: Windows 7
 I have two worksheets, one with debits categorised by date in the first column, then the amount of the debit. The second worksheet is a total of credits minus the debits and I want to sort this by date. The first column is start date, the second is end date.If I want a sum of the debits in my Total Worksheet I want to use the following formula:=SUMIFS(Debits!B2:B8,Debits!A2:A8,">Total!A2",Debits!A2:A8,"

See More: Excel Problem (referring to cell contents in function)

#1
January 13, 2012 at 04:59:54
 I believe the problem is due to the fact that you have the criteria argument enclosed in quotes. When Excel sees quotes, it assumes that everything inside the quotes is a text string. Therefore it is not picking up Total!A2 or Total!B2 as ranges.Try this syntax...it should solve both of your issues.">" & Total!A2">" & Total!B2Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
January 13, 2012 at 18:28:57
 Well that worked! Thanks DerbyDad. I tried it outside the qoutes but without the & symbol. Just one other quick question, for the part B2:B8 is there a simpler way to make it B2:Indefinite? Becasue i want to include in the search all the entries minus the heading (B1). I would assume B:B includes B1 as well...

Report •

#3
January 13, 2012 at 18:45:09
 And also is there a way to have an increment of 9 cells as you drag down? For example I want cell B2 to refer to Sheet1!A9 and cell B3 to refer back to Sheet1!A18 etc so that I can drag the formula down and fill all the cells underneath with the incremental change? Thanks :)

Report •

Related Solutions

#4
January 13, 2012 at 21:10:10
 re: is there a simpler way to make it B2:Indefinite?You could define a Dynamic Named Range and use that in your formula.If you define a Named Range that refers to this formula, the Range will be B2:B(LastRowWithData) assuming there are no blank cells within the Data:=Sheet1!\$B\$2:INDEX(Sheet1!\$B:\$B,COUNTA(Sheet1!\$B:\$B))However, is including B1 really a problem? Does including B1 cause the SUMIFS to return the wrong results?re: is there a way to have an increment of 9 cells as you drag down?If you put this in any cell it will refer to Sheet1!A9. As you drag it down, it will increment by 9 rows in each cell:=INDIRECT("'Sheet1'!A"&((ROW(A1)-1)*9+9))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •