Excel formula with linked columns 1

[Longnos]

I am working on a spreadsheet that totals up the amount of people attending a session, their age and gender. The sheet only used to contain figures down to cell 221 but as we have expanded we are now down to about 300. To find out how many are of a certain age i have been using this formula which has been working fine.In the formula K is the column that contains number of people.

=SUMPRODUCT((K27:K221)*((Age=8)+(Age=9)+(Age=10)))

However when i change the formula to anything above K221, for example:

=SUMPRODUCT((K27:K222)*((Age=8)+(Age=9)+(Age=10)))

It comes up with an #N/A error. I can tell by tracing the error that although the K column has gone down to 222, it is only reading data in the age column to 221.
Is anyone able to tell me why or what i can do to make the two columns match each other?
I understand this is a hard query to understand when you can't actaully see the spreadsheet!
Many Thanks

Longnos

 

[Gruuuu]

Because the number of cells in the "Age" range is 196. the SumProduct arrays must be equal in length.

 

[Longnos]

Thanks! How do i change the number of sells in the "age" range? Is it something i do in the formula? Cheers

 

[Gruuuu]

Which excel version are you in?

 

[Longnos]

I am in MO 2003.

 

[Gruuuu]

Insert->Names->Define
change the range definition of "Age"

I was trying to find the FAQ on this site that described the process of NEVER HAVING TO REPEAT THIS STEP when the extent of your data changes.

I couldn't find it, but here's where I learned it from

http://www.ozgrid.com/Excel/DynamicRanges.htm

 

[SkipVought]

You must also do this and it will keep your named ranges right 'up-to-date!'

Data > List > Create list

here are the HUGE advantages of using a List (A Table in 2007)

1. Your formulas will automatically propogate to the new row as you TAB to enter new data

2. Your Named Ranges will expand as you add rows.

3. You can TAB from on row to the next. For instance if your List (Table) is 3 columns wide, after you key in the data in column C, you TAB and it takes you to column A of the next row, your formulas automatically appear and the Named ranges are extended!

VOLA y'all!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Longnos]

SORTED! Thanks alot guys!

 

[xlbo]

Gruuuu - I believe you were looking for this: faq68-1331

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Longnos]

Xlbo "Gruuuu - I believe you were looking for this: FAQ68-1331: How can I rename a table as it changes size" that link is not actually what i am looking for.
Skipvought answered this for me perfectily and simply.

 

[xlbo]

My link was in response to Gruuuu's post about not being able to find a specific FAQ - I was giving them the link to it

It was not directed at you - that's why I specified "Gruuuu" at the start....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[byundt]

After you have your list sorted out, you might consider recasting your formula like this:
=SUMPRODUCT((Number)*(Age={8,9,10}))
Note how the use of array constant {8,9,10} simplifies the formula.

The above formula uses Number as the name of the dynamic named range for column K, and Age as another dynamic named range. In my test workbook, I created them using these formulas:
Age =$J$27:INDEX($J$27:$J$10000,COUNTA($J$27:$J$10000))
Number =OFFSET(Age,0,1)

If you are lucky enough to have Excel 2007 or later, you can use the new Tables feature. Tables grow and shrink automatically as you add data. You create them by selecting one or more cells in the data, then using the Insert...Table menu item. Once created, you can use header labels from the table in a formula like:
=SUMPRODUCT((Table1[Numbers])*(Table1[Ages]={8,9,10}))
Ages and Numbers were the captions for the column J and K data, respectively.

If you pass this workbook on to an Excel 2003 user, then the compatibility pack will change the Table1 formula to fixed references like:
=SUMPRODUCT((Sheet1!$K$27:$K$34)*(Sheet1!$J$27:$J$34={8,9,10}))

Brad