I am using a workbook which contains 3 sheets representing a country(Mexico,
Brazil, India) .In these sheets there are 4 columns which contain the
following:
(Customer Name) (Customer Account) (Dollar Amt) (Product Id)
There are multiple Product Id that can be charged to a customer account and
i am using a filter by product id to get sum amount of customers from each
country.How does one get a sum for all countrys combined?
Thanks
To sum the dollar amount for a specified 'Product ID' for all three
sheets, try...
=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Mexico","Brazil","India"}&"'!D2:D100"),G
2,INDIRECT("'"&{"Mexico","Brazil","India"}&"'!C2:C100")))
It assumes that the sheet names are Mexico, Brazil, and India, and that
on each sheet A2:D100 contains the data. Also, G2 contains the 'Product
ID' of interest. Adjust the range accordingly.
Hope this helps!
In article <1612B3DF-B88E-4CE8-A40E-CA0B92D04D5F@microsoft.com>,
jk <jk@discussions.microsoft.com> wrote:
> I am using a workbook which contains 3 sheets representing a country(Mexico,
> Brazil, India) .In these sheets there are 4 columns which contain the
> following:
>
> (Customer Name) (Customer Account) (Dollar Amt) (Product Id)
>
> There are multiple Product Id that can be charged to a customer account and
> i am using a filter by product id to get sum amount of customers from each
> country.How does one get a sum for all countrys combined?
>
> Thanks
Thanks,
Would this work if i did not do a filter and the various Product ID were
displayed?
"Domenic" wrote:
> To sum the dollar amount for a specified 'Product ID' for all three
> sheets, try...
>
> =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Mexico","Brazil","India"}&"'!D2:D100"),G
> 2,INDIRECT("'"&{"Mexico","Brazil","India"}&"'!C2:C100")))
>
> It assumes that the sheet names are Mexico, Brazil, and India, and that
> on each sheet A2:D100 contains the data. Also, G2 contains the 'Product
> ID' of interest. Adjust the range accordingly.
>
> Hope this helps!
>
> In article <1612B3DF-B88E-4CE8-A40E-CA0B92D04D5F@microsoft.com>,
> jk <jk@discussions.microsoft.com> wrote:
>
> > I am using a workbook which contains 3 sheets representing a country(Mexico,
> > Brazil, India) .In these sheets there are 4 columns which contain the
> > following:
> >
> > (Customer Name) (Customer Account) (Dollar Amt) (Product Id)
> >
> > There are multiple Product Id that can be charged to a customer account and
> > i am using a filter by product id to get sum amount of customers from each
> > country.How does one get a sum for all countrys combined?
> >
> > Thanks
>
Simply enter the 'Product ID' of interest in a cell, such as G2 in my
example, adjust the ranges (D2:D100 and C2:C100) accordingly, and the
formula will return the results for all sheets, regardless of whether
the data is filtered.
In article <AA538104-FFCD-46F5-8CBE-546C42C8677D@microsoft.com>,
jk <jk@discussions.microsoft.com> wrote:
> Thanks,
> Would this work if i did not do a filter and the various Product ID were
> displayed?
>
> "Domenic" wrote:
>
> > To sum the dollar amount for a specified 'Product ID' for all three
> > sheets, try...
> >
> > =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Mexico","Brazil","India"}&"'!D2:D100"),G
> > 2,INDIRECT("'"&{"Mexico","Brazil","India"}&"'!C2:C100")))
> >
> > It assumes that the sheet names are Mexico, Brazil, and India, and that
> > on each sheet A2:D100 contains the data. Also, G2 contains the 'Product
> > ID' of interest. Adjust the range accordingly.
> >
> > Hope this helps!
> >
> > In article <1612B3DF-B88E-4CE8-A40E-CA0B92D04D5F@microsoft.com>,
> > jk <jk@discussions.microsoft.com> wrote:
> >
> > > I am using a workbook which contains 3 sheets representing a
> > > country(Mexico,
> > > Brazil, India) .In these sheets there are 4 columns which contain the
> > > following:
> > >
> > > (Customer Name) (Customer Account) (Dollar Amt) (Product Id)
> > >
> > > There are multiple Product Id that can be charged to a customer account
> > > and
> > > i am using a filter by product id to get sum amount of customers from
> > > each
> > > country.How does one get a sum for all countrys combined?
> > >
> > > Thanks
> >
Thanks for the help Domenic. I tried keying manually and copy paste of you
formula but i get an error of missing parenthesis in the formula.Is there
somehthing else i could have done wrong?
"Domenic" wrote:
> Simply enter the 'Product ID' of interest in a cell, such as G2 in my
> example, adjust the ranges (D2:D100 and C2:C100) accordingly, and the
> formula will return the results for all sheets, regardless of whether
> the data is filtered.
>
> In article <AA538104-FFCD-46F5-8CBE-546C42C8677D@microsoft.com>,
> jk <jk@discussions.microsoft.com> wrote:
>
> > Thanks,
> > Would this work if i did not do a filter and the various Product ID were
> > displayed?
> >
> > "Domenic" wrote:
> >
> > > To sum the dollar amount for a specified 'Product ID' for all three
> > > sheets, try...
> > >
> > > =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Mexico","Brazil","India"}&"'!D2:D100"),G
> > > 2,INDIRECT("'"&{"Mexico","Brazil","India"}&"'!C2:C100")))
> > >
> > > It assumes that the sheet names are Mexico, Brazil, and India, and that
> > > on each sheet A2:D100 contains the data. Also, G2 contains the 'Product
> > > ID' of interest. Adjust the range accordingly.
> > >
> > > Hope this helps!
> > >
> > > In article <1612B3DF-B88E-4CE8-A40E-CA0B92D04D5F@microsoft.com>,
> > > jk <jk@discussions.microsoft.com> wrote:
> > >
> > > > I am using a workbook which contains 3 sheets representing a
> > > > country(Mexico,
> > > > Brazil, India) .In these sheets there are 4 columns which contain the
> > > > following:
> > > >
> > > > (Customer Name) (Customer Account) (Dollar Amt) (Product Id)
> > > >
> > > > There are multiple Product Id that can be charged to a customer account
> > > > and
> > > > i am using a filter by product id to get sum amount of customers from
> > > > each
> > > > country.How does one get a sum for all countrys combined?
> > > >
> > > > Thanks
> > >
>
In article <38EE821C-952F-4605-AA0B-7EC11C1CCC5C@microsoft.com>,
jk <jk@discussions.microsoft.com> wrote:
> Thanks for the help Domenic. I tried keying manually and copy paste of you
> formula but i get an error of missing parenthesis in the formula.Is there
> somehthing else i could have done wrong?
Can you post the exact formula you're using?
=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Mexico","Brazil","India"}&"'!D6:D100"),H6:H100
,INDIRECT("'"&{"Mexico","Brazil","India"}&"'!C2:C100")))
I have copied your formula but i am probably doing this incorrectly: where the
usd dollar amount exists in column "D" CUSTOMER NAME IS column "E" CUSTOMER
ACCT COLUMN "F" AND THE PRODUCTID is column "H". I used H6:H100 since this is
the range in which the productID exists.
"Domenic" wrote:
> In article <38EE821C-952F-4605-AA0B-7EC11C1CCC5C@microsoft.com>,
> jk <jk@discussions.microsoft.com> wrote:
>
> > Thanks for the help Domenic. I tried keying manually and copy paste of you
> > formula but i get an error of missing parenthesis in the formula.Is there
> > somehthing else i could have done wrong?
>
> Can you post the exact formula you're using?
>
In that case, try...
=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Mexico","Brazil","India"}&"'!H2:H100"),G
2,INDIRECT("'"&{"Mexico","Brazil","India"}&"'!D2:D100")))
....where G2 contains the Product ID of interest. In other words, enter
in G2 the Product ID for which you would like an overall sum, and the
formula will sum Column D on each sheet where the corresponding cell in
Column H contains the Product ID specified in G2.
In article <B5E7FDB9-C69C-4263-AAA7-010A865AF224@microsoft.com>,
jk <jk@discussions.microsoft.com> wrote:
> =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Mexico","Brazil","India"}&"'!D6:D100"),H6:H10
> 0
> ,INDIRECT("'"&{"Mexico","Brazil","India"}&"'!C2:C100")))
> I have copied your formula but i am probably doing this incorrectly: where
> the
> usd dollar amount exists in column "D" CUSTOMER NAME IS column "E" CUSTOMER
> ACCT COLUMN "F" AND THE PRODUCTID is column "H". I used H6:H100 since this is
> the range in which the productID exists.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks