Using Name Ranges as an Array

[excelforum123]

Dear all,

I'm trying to use =SUMPRODUCT() for its very basic use. However, I have defined a few named ranges.

For example, I have these few named ranges.
"WeekdayPrice" = 20
"WeekendPrice" = 50

I am trying to use this in SUMPRODUCT(), in the sense
$A$1:$B$1 contains the attendance for weekday and weekend respectively.

Please Login or Register  to view this content.

However, this does not work.

Please assist? This is a very simplified version of it, as I have used OFFSET, INDEX and MATCH to find the attendance for weekday and weekend. I have verified that it works correctly.

Thank you very much.

[:) Sixthsense :)]

You can't use named ranges, range or cell references in array {}.

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

[excelforum123]

What I'm trying to achieve is actually, sum of all the attendance * price

Please Login or Register  to view this content.

But, I have alot of prices (actually about 30 prices) to consider, hence, I've used OFFSET after INDEX and MATCH to select an array and trying to use SUMPRODUCT.

[:) Sixthsense :)]

Extend the Range if required.

Please Login or Register  to view this content.

[excelforum123]

Sorry. I don't really understand where you are coming from.

Basically I have about 30 prices and 30 list of attendances.

So

Please Login or Register  to view this content.

.

For each Attendance, I'm using INDEX and MATCH to find the right attendance for the right employee.

[:) Sixthsense :)]

Sorry I am unable to suggest something on assumption instead show us a sample workbook with dummy data by convering the expected output for better understanding

[Kevin UK]

Hi excelforum123

How about using SUMIFS or may be: =SUM(SUMIF(YourCriteriaRange,{"Criteria1","Criteria2","Criteria3","Etc"},YourSumRange))

[:) Sixthsense :)]

Hi Kevin UK,

Did you seen the text of Post #2?

[daddylonglegs]

The obvious answer would appear to be

=SUMPRODUCT(attendances,prices)

where attendances is a contiguous range of all 30 attendances, prices is a similarly oriented contiguous range of all 30 prices

I'm assuming that you can't do that - is that because the cells are not in a single range or you just don't want to name the whole range?