Dynamic name reference in SUMPRODUCT

[Guest]

Formula:
SUMPRODUCT((ACCOUNT)*(AMOUNT)*(Period="2006-08"))
works fine... it sums the amounts where period equak 2006-08

but

Forumla:
SUMPRODUCT(($A$2)*(AMOUNT)*(Period="2006-08"))
doesn't work...

$A$2="ACCOUNT"

ACCOUNT=(A2:A65535)

Data table
Account Amount Period
7830 23400 2006-08
3220 -99900 2006-12
3440 -7100 2006-08
3560 2700 2006-08
8540 7900 2006-12

Is there anyone that could help, plse

Best Regards

 

[Guest]

Don't really see how this works

=SUMPRODUCT((ACCOUNT)*(AMOUNT)*(Period="2006-08"))

this will multiply the account number(s) by the amount(s)

If you are summing Amounts for a specific account number that would be
something like

=SUMPRODUCT((ACCOUNT=3220)*(AMOUNT)*(Period="2006-08"))

What do you have in A2 - a specific account number?

 

[Guest]

Thanks for bothering...

The formula sums all the AMOUNTs oi rows where PERIOD equals "2006-08" and
ACCOUNT is not null.

I would like to put "ACCOUNT" in cell $A$2 and put the cell reference $A$2
in the formula instead.

BR


"daddylonglegs" skrev:

 

[Guest]

Try
Indirect($A$2)

Thanks for bothering...

The formula sums all the AMOUNTs oi rows where PERIOD equals "2006-08" and
ACCOUNT is not null.

I would like to put "ACCOUNT" in cell $A$2 and put the cell reference $A$2
in the formula instead.

BR


"daddylonglegs" skrev:

 

[Arvi Laanemets]

Hi

INDIRECT converts string range description to range. But maybe you start
from start - what do you want to do at all? I have a feeling there will be
some better solution.

Btw, having account range described in A2, you have always to check the
number of rows in ranges Amount and Period, and compare it with number of
rows you'll get from range described in A2 - SUMPRODUCT will work only, when
it is same (all ranges involved must be of same dimension).

And your original formula must be
=SUMPRODUCT((ACCOUNT<>0)*(Period="2006-08")*(AMOUNT))
, or
=SUMPRODUCT((ACCOUNT<>"")*(Period="2006-08")*(AMOUNT))

And I myself prefer other syntax, like
=SUMPRODUCT(--(ACCOUNT<>""),--(Period="2006-08"),AMOUNT)

 

[Guest]

I would like to select intervals and name them in order to use the same
intervals in many sheets:

In A2 the interval could be (Account=>3000) * (Account=<3999) and name SALES
In A3 the interval could be (Account=>4000) * (Account=<4999) and name
OPERATIONAL_COST
In A4 the interval could be (Account=>5000) * (Account=<6999) and name
OTHER_EXTERNAL_COST
In A5 the interval could be (Account=>7000) * (Account=<7699) and name
LABOR_RELATED_COST

BR

"Arvi Laanemets" skrev: