Manipulating Formula

Hello,

On Aug 3 of my orginal posting i was provided an excellent array formula to
my request below:
Two for sure dumb questions, why have a column "D" if coulmn "A" is for
accounts,column "B" is for corresponding dollar amounts and column "C" is for
the results of sumed account values? Do you hit cntrl shift enter first on a
cell then enter the formula?

"Bob Phillips" wrote:

> C1: =A1
> C2: =IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$20&""),0)),"",
> INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(C$1:C1,$A$1:$A$2
> 0&""),0)))
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> just Enter.
>
> Copy C2 down as far as you need
>
> D1: =SUMIF(A:A,C1,B:B)
>
> and copy down
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "jk" <jk@discussions.microsoft.com> wrote in message
> news:170719BC-15C6-4D3C-B5E5-C27100015191@microsoft.com...
> > I have column A that contains many account numbers and i have column B
> which
> > is a dollar value that corresponds to each account.
> >
> > A B
> > 10023 $25.00
> > 10024 $20.00
> > 10025 $25.00
> > 10023 $25.00
> > 10026 $25.00
> > Is it possible to assign cells in column C to sum the amounts which equal
> > the accounts. For example account 10023 sums up to $50.00 and i would like
> > each time the account of 10023 is entered into column A ,it would
> accumulate
> > the amount in a cell of column C .........can this be done?
> >
I now need to manipulate the formula to a set of different columns and row.
Where in this formula does it correspond to row and column? For example, if
instead of using columns A,B,C,D and row 1 and now wanted to use this
formula using columns D,E,F,G and starting on row 6.......how do i do that?

after you type the formula in the cell, hit Ctrl+Shift+Enter rather than
just Enter to terminate formula entry.

--
regards,
Tom Ogilvy


"jk" <jk@discussions.microsoft.com> wrote in message
news:1A05CC04-8E94-475A-81D3-A2C790595528@microsoft.com...
> Hello,
>
> On Aug 3 of my orginal posting i was provided an excellent array formula
> to
> my request below:
> Two for sure dumb questions, why have a column "D" if coulmn "A" is for
> accounts,column "B" is for corresponding dollar amounts and column "C" is
> for
> the results of sumed account values? Do you hit cntrl shift enter first on
> a
> cell then enter the formula?
>
> "Bob Phillips" wrote:
>
>> C1: =A1
>> C2: =IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$20&""),0)),"",
>> INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(C$1:C1,$A$1:$A$2
>> 0&""),0)))
>>
>> which is an array formula, it should be committed with Ctrl-Shift-Enter,
>> not
>> just Enter.
>>
>> Copy C2 down as far as you need
>>
>> D1: =SUMIF(A:A,C1,B:B)
>>
>> and copy down
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (replace somewhere in email address with gmail if mailing direct)
>>
>> "jk" <jk@discussions.microsoft.com> wrote in message
>> news:170719BC-15C6-4D3C-B5E5-C27100015191@microsoft.com...
>> > I have column A that contains many account numbers and i have column B
>> which
>> > is a dollar value that corresponds to each account.
>> >
>> > A B
>> > 10023 $25.00
>> > 10024 $20.00
>> > 10025 $25.00
>> > 10023 $25.00
>> > 10026 $25.00
>> > Is it possible to assign cells in column C to sum the amounts which
>> > equal
>> > the accounts. For example account 10023 sums up to $50.00 and i would
>> > like
>> > each time the account of 10023 is entered into column A ,it would
>> accumulate
>> > the amount in a cell of column C .........can this be done?
>> >
> I now need to manipulate the formula to a set of different columns and
> row.
> Where in this formula does it correspond to row and column? For example,
> if
> instead of using columns A,B,C,D and row 1 and now wanted to use this
> formula using columns D,E,F,G and starting on row 6.......how do i do
> that?

Please read to the bottom of my posting since it deals with the formula and
how to make changes.

Thanks

"Tom Ogilvy" wrote:

> after you type the formula in the cell, hit Ctrl+Shift+Enter rather than
> just Enter to terminate formula entry.
>
> --
> regards,
> Tom Ogilvy
>
>
> "jk" <jk@discussions.microsoft.com> wrote in message
> news:1A05CC04-8E94-475A-81D3-A2C790595528@microsoft.com...
> > Hello,
> >
> > On Aug 3 of my orginal posting i was provided an excellent array formula
> > to
> > my request below:
> > Two for sure dumb questions, why have a column "D" if coulmn "A" is for
> > accounts,column "B" is for corresponding dollar amounts and column "C" is
> > for
> > the results of sumed account values? Do you hit cntrl shift enter first on
> > a
> > cell then enter the formula?
> >
> > "Bob Phillips" wrote:
> >
> >> C1: =A1
> >> C2: =IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$20&""),0)),"",
> >> INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(C$1:C1,$A$1:$A$2
> >> 0&""),0)))
> >>
> >> which is an array formula, it should be committed with Ctrl-Shift-Enter,
> >> not
> >> just Enter.
> >>
> >> Copy C2 down as far as you need
> >>
> >> D1: =SUMIF(A:A,C1,B:B)
> >>
> >> and copy down
> >>
> >> --
> >> HTH
> >>
> >> Bob Phillips
> >>
> >> (replace somewhere in email address with gmail if mailing direct)
> >>
> >> "jk" <jk@discussions.microsoft.com> wrote in message
> >> news:170719BC-15C6-4D3C-B5E5-C27100015191@microsoft.com...
> >> > I have column A that contains many account numbers and i have column B
> >> which
> >> > is a dollar value that corresponds to each account.
> >> >
> >> > A B
> >> > 10023 $25.00
> >> > 10024 $20.00
> >> > 10025 $25.00
> >> > 10023 $25.00
> >> > 10026 $25.00
> >> > Is it possible to assign cells in column C to sum the amounts which
> >> > equal
> >> > the accounts. For example account 10023 sums up to $50.00 and i would
> >> > like
> >> > each time the account of 10023 is entered into column A ,it would
> >> accumulate
> >> > the amount in a cell of column C .........can this be done?
> >> >
> > I now need to manipulate the formula to a set of different columns and
> > row.
> > Where in this formula does it correspond to row and column? For example,
> > if
> > instead of using columns A,B,C,D and row 1 and now wanted to use this
> > formula using columns D,E,F,G and starting on row 6.......how do i do
> > that?
>
>
>

The formula Bob posted for column C creates a list of unique account numbers.
The formula in column D does a sumif on the original Data (A:B) and give you
a sum for each unique account number. If you want to that to start in D6:


F6: =D6
F7:
=IF(ISERROR(MATCH(0,COUNTIF(F$6:F6,$D$6:$D$25&""),0)),"",INDEX(IF(ISBLANK($D$6:$D$25),"",$D$6:$D$25),MATCH(0,COUNTIF(F$6:F6,$D$6:$D$25&""),0)))

Entered with Ctrl+Shift+Enter


G6:= Sumif($D$6:$D$1000,$F6,$E$6:$E$1000)

select F6:G6 and pull down

--
Regards,
Tom Ogilvy


"jk" wrote:

> Please read to the bottom of my posting since it deals with the formula and
> how to make changes.
>
> Thanks
>
> "Tom Ogilvy" wrote:
>
> > after you type the formula in the cell, hit Ctrl+Shift+Enter rather than
> > just Enter to terminate formula entry.
> >
> > --
> > regards,
> > Tom Ogilvy
> >
> >
> > "jk" <jk@discussions.microsoft.com> wrote in message
> > news:1A05CC04-8E94-475A-81D3-A2C790595528@microsoft.com...
> > > Hello,
> > >
> > > On Aug 3 of my orginal posting i was provided an excellent array formula
> > > to
> > > my request below:
> > > Two for sure dumb questions, why have a column "D" if coulmn "A" is for
> > > accounts,column "B" is for corresponding dollar amounts and column "C" is
> > > for
> > > the results of sumed account values? Do you hit cntrl shift enter first on
> > > a
> > > cell then enter the formula?
> > >
> > > "Bob Phillips" wrote:
> > >
> > >> C1: =A1
> > >> C2: =IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$20&""),0)),"",
> > >> INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(C$1:C1,$A$1:$A$2
> > >> 0&""),0)))
> > >>
> > >> which is an array formula, it should be committed with Ctrl-Shift-Enter,
> > >> not
> > >> just Enter.
> > >>
> > >> Copy C2 down as far as you need
> > >>
> > >> D1: =SUMIF(A:A,C1,B:B)
> > >>
> > >> and copy down
> > >>
> > >> --
> > >> HTH
> > >>
> > >> Bob Phillips
> > >>
> > >> (replace somewhere in email address with gmail if mailing direct)
> > >>
> > >> "jk" <jk@discussions.microsoft.com> wrote in message
> > >> news:170719BC-15C6-4D3C-B5E5-C27100015191@microsoft.com...
> > >> > I have column A that contains many account numbers and i have column B
> > >> which
> > >> > is a dollar value that corresponds to each account.
> > >> >
> > >> > A B
> > >> > 10023 $25.00
> > >> > 10024 $20.00
> > >> > 10025 $25.00
> > >> > 10023 $25.00
> > >> > 10026 $25.00
> > >> > Is it possible to assign cells in column C to sum the amounts which
> > >> > equal
> > >> > the accounts. For example account 10023 sums up to $50.00 and i would
> > >> > like
> > >> > each time the account of 10023 is entered into column A ,it would
> > >> accumulate
> > >> > the amount in a cell of column C .........can this be done?
> > >> >
> > > I now need to manipulate the formula to a set of different columns and
> > > row.
> > > Where in this formula does it correspond to row and column? For example,
> > > if
> > > instead of using columns A,B,C,D and row 1 and now wanted to use this
> > > formula using columns D,E,F,G and starting on row 6.......how do i do
> > > that?
> >
> >
> >