Getting sumifs and dynamic ranges to work together

**DRFILL** · 12-04-2012, 04:06 PM

I have a dynamic table and 4 named ranges coming from the table.

I have the following formula in the reference of the named range.
=OFFSET(Ledger!$B$2,0,0,COUNT(Ledger!$B$2:Ledger!$B$2001),1)

On a different sheet I have the following formula to add up the totals based on specific criteria.
=SUMIFS(Withdraws,notes,"="&$J4,Date,"<"&INDEX(DTlookup,MATCH($I$4,months,0),3),Date,">="&INDEX(DTlookup,MATCH($I$4,months,0),4))

I keep getting a value error. however if I change the named range reference to
=OFFSET(Ledger!$B$2,0,0,COUNT(Ledger!$B$2:Ledger!$B$66),1) *"the table currently stops at row 66"* the sumifs formula works but it stops being dynamic and does not grow when new entries are entered.

*note this is my first time trying to make a dynamic table or dynamic ranges.

any help would be much appreciated.

**FDibbins** · 12-04-2012, 04:25 PM

hard to say without seeing a sample workbook, but looking at your sumifs(), it looks wrong...

=SUMIFS(Withdraws,notes,"="&$J4,Date,"<"&INDEX(DTlookup,MATCH($I$4,months,0),3),Date,">="&INDEX(DTlookup,MATCH($I$4,months,0),4)

sumifs(criteria1-range, criteria1,criteria2-range, criteria2, etc....

the bolded part looks wrong? looks like criteria1-range, criteria1-range, criteria1 ?

**DRFILL** · 12-04-2012, 11:28 PM

budget.11.xlsx

I don't think the sumifs is the problem. Withdraws is the sum-range which is before the 1st criteria in sumifs.

If I change the named ranges (withdraws,notes and date) to Ledger!$E2:$E2000... it will work, but I don't want to have to come back into the file and change the formula later. And I would like to refer to the ranges by name instead of having to click and drag.

**Teethless mama** · 12-05-2012, 12:20 AM

You no need to create the named ranges when you are using "Table" format. The table is already created name ranges for each columns.

**DRFILL** · 12-05-2012, 12:42 AM

hmmm, this is interesting. Okay, It works, thank you. However you have raised many more question about tables that I will have to figure out.
To be honest the reasons I went with a table were because by hitting tab on the the last cell it added a line and the alternating colors(something I want in the file)

Is a table format the best way to make this work?

**FDibbins** · 12-05-2012, 12:52 AM

sorry, dont know where my head was when I posted that lol

**Teethless mama** · 12-05-2012, 01:36 AM

Is a table format the best way to make this work? It convience to use without reinvented the wheel

I look some of your defined name ranges you created are not even correct, that why your formula is not working when you use those name ranges.

**DRFILL** · 12-05-2012, 11:11 AM

that does not suprise me. what is not correct about them?

FDibbins, no worries. I would be the richest man in the world if i had a nickle for every time I made a mistake.

**daddylonglegs** · 12-05-2012, 11:53 AM

It's only appropriate to use a dynamic named range like this one

=OFFSET(Ledger!$B$2,0,0,COUNT(Ledger!$B$2:Ledger!$B$2001),1)

where you have continuous numeric data.

You have data down to row 70 but some blanks so, as there are only 18 deposit values the above just gives you the range Ledger!$B$2:$B$19, thus missing some of the values.

As your other named ranges are based on the number of the values in different columns they are all different sizes - SUMIFS requires all the ranges to be the same size, so you get #VALUE! error.

Try creating named ranges based on a column where all rows are populated, so Dates is OK as

=OFFSET(Ledger!$A$2,0,0,COUNTA(Ledger!$A$2:$A$2001),1)

but it's then best to base all your other named ranges on the COUNTA from that range, e.g. Withdraws can be

=OFFSET(Ledger!$E$2,0,0,COUNTA(Ledger!$A$2:$A$2001),1)

with just the start cell changed in the OFFSET function. Your other named ranges should follow that same pattern, then your SUMIFS should work