query with sumproduct

[taylo40]

Hello,

I wonder whether anybody can help me with a sumproduct query ?

I have created a formula in Excel which returns the answer that I require:-

=SUMPRODUCT(('Meridian Costs'!$L$2:$L$219=Summary!$B12)*('Meridian Costs'!$O$2:$O$219=Summary!C$10)*('Meridian Costs'!$Q$2:$Q$219))

The Sheet called 'Meridian Costs' will grow over time, this will mean that I will have to amend to formula to replace the '$L$219' with the last row.

I have been asked whether I can replace the exact value in the formulas with a range name. I have created a range called "name" and highlighted all of the column.

The formula now looks like:-

=SUMPRODUCT((name=Summary!$B11)*('Meridian Costs'!$O$2:$O$219=Summary!C$10)*('Meridian Costs'!$Q$2:$Q$219))

When I press enter I get the #NUM! error.

Any ideas why I am getting this error message ?

Regards

 

[xlbo]

Do your range names include the header row ?

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

 

[xlhelp]

Perhaps replacing $L$219 with

OFFSET(L2,COUNTA(L:L)-1,0,1,1)

will help, assuming there is no other data below row 219 and L1 contains columns heading.

and similarly replace cell for O219 and Q219

Member AAA - Abolish Abused Abbreviations

 

[taylo40]

Hello both,

The range in column L will contain a header record.

The purpose for defining the range name was that the data range would continue to get larger over time and the user would have to amend the formula to replace L219 to L350 (for example)


Hope this is clear,

Jamie

 

[SkipVought]

look down thru $Q$2:$Q$219.

You have at least ONE non-numeric value.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[xlbo]

To answer the error question, it is because you have your headers included in your range - tha range should include your values only

To create a dynamic named range, utilise the OFFSET formula.

Go Insert>Name>Define as per normal but instead of entering a range in the "refersto" box, if L2:L219 is "name" then instead of that, enter:

=OFFSET($L$2,,,counta($L:$L)-1,1)

Do the same for your other named ranges, changing the L for the appropriate column letter. The -1 takes account of the header row. To see the range, highlight it in the list of names and click 'into' the formula - the range will be surrounded by dashed lines.

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

 

[taylo40]

Hello Chaps,

The Offset function has sorted the probem. All I need to to now is understand how it works !!!!!

Cheers


Jamie

 

[xlbo]

have a look at the help file for the offset function for more detail but essentially:

Offset descibes a range as follows

=Offset(Starting_Cell, Top_Left_Range_Cell_Starts_From_This_Many_Rows_Down,
Top_Left_Range_Cell_Starts_From_This_Many_Columns_Across,
Range_Is_This_Many_Rows_Deep,
Range_Is_This_Many_Columns_Across)

The result will be a rectangular range. As a test, just create a new range name using the offset formula but place numbers in rather than formulae - adjust the numbers and observe how the range size and position changes

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

 

[KenWright]

You could also have tried the following syntax which also gets you round the fact that you may have text in the fields:-

=SUMPRODUCT(--(name=Summary!$B11),--('Meridian Costs'!$O$2:$O$219=Summary!C$10),('Meridian Costs'!$Q$2:$Q$219))

Regards
Ken..............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------