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

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Johnny Meredith" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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.

"Johnny Meredith" wrote:

> 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
>
>

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

JMB,

Works great. Thanks for the help.

Johnny