Can this be done with Domain Functions?

[Johnny Meredith]

Hi all,

I have a workbook with three tabs:

Report
Data
Groupings

Report has the following info:

Group Amount
A <need formulas here>
B
C

Data has a list of accounts and balances. Groupings has a list of all
possible accounts and what group they below to (A,B,C,etc.)

My question is: on the report tab, can I use a domain function to sum
all amounts from the Data tab into their proper groupings? The groups
will have to be looked up on the Groupings tab by account. This would
be an elegant solution if possible. Otherwise, I'm going to have to
duplicate data (i.e. - do another report with the accounts, groupings,
and balance and use plain vanilla sumifs) or do something in VBA to
generate the Report tab.

Thanks in advance,
Johnny

 

[Max]

One try ..

In sheet: Report,

Put in B2, copy down:
=SUMPRODUCT((Groupings!$B$2:$B$100=A2)
*(Data!$A$2:$A$100<>""),Data!$B$2:$B$100)

Adapt the ranges to suit ..

 

[Guest]

Another way - I'm assuming that your groupings worksheet contains all
possible account numbers, the data worksheet contains a subset of the account
numbers found in Groupings (ie - two different sized, which would defeat
normal array multiplication).

I'm assuming the groupings you are trying to sum begin in cell A1 of Report,
Data A1:A7 lists account numbers, Data B1:B7 lists account amounts, Groupings
A1:A10 lists account numbers, and Groupings B1:B10 list account group.

In cell B1 of Data enter:

=SUM(IF(ISNA(MATCH(Data!$A$1:$A$7,IF(Groupings!$B$1:$B$10=A1,Groupings!$A$1:$A$10,""),0)),0,Data!$B$1:$B$7))

and copy down. Note this is an array formula, you will need to enter with
Control+Shift+Enter.

Change ranges as necessary.

 

[Max]

Sorry, pl dismiss the earlier suggestion, which doesn't work if there's
duplicate account listings in Data (likely the case)

Go with JMB's offering ..

---

 

[Johnny Meredith]

JMB,

Works great. Thanks for the help.

Johnny