SUMIFS with named range not working

[mjancic]

Hi,

I need help with following formula:

SUMIFS(INDIRECT(Data!$D$33;0);Customer_GSFA;'Quick Report'!$C$5;Month_Period;$B7)

I don't know why it's not working, I get #REF!..

INDIRECT is connected to Data!$D$33 which contains named range XXX.

If type XXX directly into formula instead of using indirect formula works.

Please help!!!

Thanks in advance
M

 

[Aladin Akyurek]

Hi,

I need help with following formula:

SUMIFS(INDIRECT(Data!$D$33;0);Customer_GSFA;'Quick Report'!$C$5;Month_Period;$B7)

I don't know why it's not working, I get #REF!..

INDIRECT is connected to Data!$D$33 which contains named range XXX.

If type XXX directly into formula instead of using indirect formula works.

Please help!!!

Thanks in advance
M

How is XXX defined? With OFFSET?

Also, on which sheet are you invoking this formula?

 

[mjancic]

XXX if defined with vlookup.

This formula is in sheet "Quick Report Internal"

Thanks

 

[Aladin Akyurek]

XXX if defined with vlookup.

This formula is in sheet "Quick Report Internal"

Thanks

Care to show the formula which defines that XXX?
How many XXX's do we have?

Is the sheet name here

'Quick Report'!$C$5

correct?

 

[mjancic]

Hi,

This is the formula

=VLOOKUP($A$32;$B$33:$C$36;2;0), it's in sheet "Data"

There are 4 XXX, this values represents named ranges

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Base_Rev_BER</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>TTL_Rev_BER</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Base_Rev_LCY</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>TTL_Rev_LCY</TD></TR></TBODY></TABLE>


I want to have dynamic sum_range in formula SUMIFS.

I have dropdown list with 4 values:

Base Revenue - EUR
Total Revenue - EUR
Base Revenue - HRK
Total Revenue - HRK

then I use vlookup to look for correct named range, so when you choose for example Base Revenue - EUR sum_range should use named range Base_Rev_BER, but it returns #REF!.

M

 

[Aladin Akyurek]

How is

Base_Rev_BER

defined? With OFFSET?

 

[mjancic]

Yes, all 4 named ranges are defined with offset

=OFFSET('Main Extract'!$AC$1;1;0;COUNTA('Main Extract'!$A:$A))

M

 

[Aladin Akyurek]

Yes, all 4 named ranges are defined with offset

=OFFSET('Main Extract'!$AC$1;1;0;COUNTA('Main Extract'!$A:$A))

M

A side note...

Define Lrec as referring to:

=MATCH(9,99999999999999E+307;'Main Extract'!$A:$A)

assuming that column A on Main Extract is numeric.

Any one of the following definitions for Base_Rev_BER would be better:


='Main Extract'!$AC$1:INDEX('Main Extract'!$AC:$AC;Lrec)

=OFFSET('Main Extract'!$AC$1;0;0;Lrec-MIN(ROW('Main Extract'!$AC$1))+1)

Now returning back to your SumIfs formula, try something like:

=SUMIFS(
    CHOOSE(MATCH(DropDownCell;DropDownList;0);
        Base_Rev_BER;TTL_Rev_BER;Base_Rev_LCY;TTL_Rev_LCY);<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; WIDTH: 48pt; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" height=20 width=64>Base_Rev_BER</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" height=20>TTL_Rev_BER</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" height=20>Base_Rev_LCY</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" height=20>TTL_Rev_LCY</TD></TR></TBODY></TABLE>    Customer_GSFA;'Quick Report'!$C$5;
    Month_Period;$B7)

DropDownList consists of:

Base Revenue - EUR
Total Revenue - EUR
Base Revenue - HRK
Total Revenue - HRK

and DropDownCell is a choice from the DropDownList.