+ Reply to Thread
Results 1 to 11 of 11

Sum cells based on a row variable and seperate column variable

  1. #1
    CheeseHeadTransplant
    Guest

    Sum cells based on a row variable and seperate column variable

    I have a spreadsheet with 3 sheets of date. On all 3 column A is the same
    and Row 3 is the same. Column A is labeled Weave_Market_MTD and Row 3 is
    Weave_Date_MTD. I need to sum all like items in Column A for a Date I have
    in Cell D7 of the summary page. Before changing this sheet Column A did not
    have two cells the same so I used the formula

    =INDEX(Weave!$1:$65536,MATCH($A15,Weave!A:$A,0),MATCH($D$7,Weave!$3:$3,0))

    Now I have to add all the items for the same Market. This is what my sheet
    looks like now:
    09/01/05 09/02/05 09/03/05 09/04/05
    09/05/05
    C/R 5 10 15
    20 25
    EIFS 5 10 15
    20 25
    Export 5 10 15
    20 25
    OEM 5 10 15
    20 25
    Export 5 10 15
    20 25
    Export 5 10 15
    20 25
    OEM 5 10 15
    20 25

    If I need the total for 09/03/05 I need C/R = 15, EIFS = 15, OEM = 30 and
    Export = 45.


  2. #2
    Domenic
    Guest

    Re: Sum cells based on a row variable and seperate column variable

    Assumptions:

    On sheet 'Weave'...

    B3:F3 contains your dates

    A4:A10 contains C/R, EIFS, etc.

    B4:F10 contains your data

    On your summary sheet...

    B1 contains your date of interest, such as 9/3/05

    A2:A5 contains C/R, EIFS, OEM, and Export

    Formula:

    On your summary sheet...

    B2, copied down:

    =SUMIF(Weave!$A$4:$A$10,$A2,INDEX(Weave!$B$4:$F$10,0,MATCH(B$1,Weave!$B$3
    :$F$3,0)))

    Note that if you continue to add the dates along the first row of your
    summary sheet, the formula can be copied across to other columns giving
    you a summary for each date.

    Hope this helps!

    In article <06C49AE4-761A-4AB6-A629-9B1EBA66618A@microsoft.com>,
    "CheeseHeadTransplant"
    <CheeseHeadTransplant@discussions.microsoft.com> wrote:

    > I have a spreadsheet with 3 sheets of date. On all 3 column A is the same
    > and Row 3 is the same. Column A is labeled Weave_Market_MTD and Row 3 is
    > Weave_Date_MTD. I need to sum all like items in Column A for a Date I have
    > in Cell D7 of the summary page. Before changing this sheet Column A did not
    > have two cells the same so I used the formula
    >
    > =INDEX(Weave!$1:$65536,MATCH($A15,Weave!A:$A,0),MATCH($D$7,Weave!$3:$3,0))
    >
    > Now I have to add all the items for the same Market. This is what my sheet
    > looks like now:
    > 09/01/05 09/02/05 09/03/05 09/04/05
    > 09/05/05
    > C/R 5 10 15
    > 20 25
    > EIFS 5 10 15
    > 20 25
    > Export 5 10 15
    > 20 25
    > OEM 5 10 15
    > 20 25
    > Export 5 10 15
    > 20 25
    > Export 5 10 15
    > 20 25
    > OEM 5 10 15
    > 20 25
    >
    > If I need the total for 09/03/05 I need C/R = 15, EIFS = 15, OEM = 30 and
    > Export = 45.


  3. #3
    Deeds
    Guest

    Re: Sum cells based on a row variable and seperate column variable

    Domenic,
    I have the same problem, however I made yours work for when it is trying
    to match 1 criteria in the column and 1 criteria in the row...however I would
    like to add 2 more critera for the rows and 2 more criteria for the columns.
    i.e. sum the range data if 3 cells in the column title match AND 3 cells in
    the rows match. Can you help? A4, B4, C4 AND D1, D2, D3 must all match my
    criteria for each cell. I have been struggling with this for awhile...any
    ideas? Thanks

    "Domenic" wrote:

    > Assumptions:
    >
    > On sheet 'Weave'...
    >
    > B3:F3 contains your dates
    >
    > A4:A10 contains C/R, EIFS, etc.
    >
    > B4:F10 contains your data
    >
    > On your summary sheet...
    >
    > B1 contains your date of interest, such as 9/3/05
    >
    > A2:A5 contains C/R, EIFS, OEM, and Export
    >
    > Formula:
    >
    > On your summary sheet...
    >
    > B2, copied down:
    >
    > =SUMIF(Weave!$A$4:$A$10,$A2,INDEX(Weave!$B$4:$F$10,0,MATCH(B$1,Weave!$B$3
    > :$F$3,0)))
    >
    > Note that if you continue to add the dates along the first row of your
    > summary sheet, the formula can be copied across to other columns giving
    > you a summary for each date.
    >
    > Hope this helps!
    >
    > In article <06C49AE4-761A-4AB6-A629-9B1EBA66618A@microsoft.com>,
    > "CheeseHeadTransplant"
    > <CheeseHeadTransplant@discussions.microsoft.com> wrote:
    >
    > > I have a spreadsheet with 3 sheets of date. On all 3 column A is the same
    > > and Row 3 is the same. Column A is labeled Weave_Market_MTD and Row 3 is
    > > Weave_Date_MTD. I need to sum all like items in Column A for a Date I have
    > > in Cell D7 of the summary page. Before changing this sheet Column A did not
    > > have two cells the same so I used the formula
    > >
    > > =INDEX(Weave!$1:$65536,MATCH($A15,Weave!A:$A,0),MATCH($D$7,Weave!$3:$3,0))
    > >
    > > Now I have to add all the items for the same Market. This is what my sheet
    > > looks like now:
    > > 09/01/05 09/02/05 09/03/05 09/04/05
    > > 09/05/05
    > > C/R 5 10 15
    > > 20 25
    > > EIFS 5 10 15
    > > 20 25
    > > Export 5 10 15
    > > 20 25
    > > OEM 5 10 15
    > > 20 25
    > > Export 5 10 15
    > > 20 25
    > > Export 5 10 15
    > > 20 25
    > > OEM 5 10 15
    > > 20 25
    > >
    > > If I need the total for 09/03/05 I need C/R = 15, EIFS = 15, OEM = 30 and
    > > Export = 45.

    >


  4. #4
    Domenic
    Guest

    Re: Sum cells based on a row variable and seperate column variable

    Can you provide a small sample of your data, along with expected results?

    In article <48F7AF49-E535-46F7-A0B8-14D43AFC0A8A@microsoft.com>,
    "Deeds" <Deeds@discussions.microsoft.com> wrote:

    > Domenic,
    > I have the same problem, however I made yours work for when it is trying
    > to match 1 criteria in the column and 1 criteria in the row...however I would
    > like to add 2 more critera for the rows and 2 more criteria for the columns.
    > i.e. sum the range data if 3 cells in the column title match AND 3 cells in
    > the rows match. Can you help? A4, B4, C4 AND D1, D2, D3 must all match my
    > criteria for each cell. I have been struggling with this for awhile...any
    > ideas? Thanks


  5. #5
    Deeds
    Guest

    Re: Sum cells based on a row variable and seperate column variable

    Can you go to my post Vlookup "crosstab"....you will find an example there.
    I just posted it....thanks!

    "Domenic" wrote:

    > Can you provide a small sample of your data, along with expected results?
    >
    > In article <48F7AF49-E535-46F7-A0B8-14D43AFC0A8A@microsoft.com>,
    > "Deeds" <Deeds@discussions.microsoft.com> wrote:
    >
    > > Domenic,
    > > I have the same problem, however I made yours work for when it is trying
    > > to match 1 criteria in the column and 1 criteria in the row...however I would
    > > like to add 2 more critera for the rows and 2 more criteria for the columns.
    > > i.e. sum the range data if 3 cells in the column title match AND 3 cells in
    > > the rows match. Can you help? A4, B4, C4 AND D1, D2, D3 must all match my
    > > criteria for each cell. I have been struggling with this for awhile...any
    > > ideas? Thanks

    >


  6. #6
    Domenic
    Guest

    Re: Sum cells based on a row variable and seperate column variable

    See your other post...

    In article <A85682BF-ED88-4DFE-BBAF-844CC5CC04D8@microsoft.com>,
    "Deeds" <Deeds@discussions.microsoft.com> wrote:

    > Can you go to my post Vlookup "crosstab"....you will find an example there.
    > I just posted it....thanks!


  7. #7
    CheeseHeadTransplant
    Guest

    Re: Sum cells based on a row variable and seperate column variable

    This seems to be working except for one small problem... it is giving the
    results for the wrong day. It is always off 3 days. If I query 09/01/05 it
    is giving the results for 09/04/05. I can't figure out why the strange
    result.

    "Domenic" wrote:

    > Assumptions:
    >
    > On sheet 'Weave'...
    >
    > B3:F3 contains your dates
    >
    > A4:A10 contains C/R, EIFS, etc.
    >
    > B4:F10 contains your data
    >
    > On your summary sheet...
    >
    > B1 contains your date of interest, such as 9/3/05
    >
    > A2:A5 contains C/R, EIFS, OEM, and Export
    >
    > Formula:
    >
    > On your summary sheet...
    >
    > B2, copied down:
    >
    > =SUMIF(Weave!$A$4:$A$10,$A2,INDEX(Weave!$B$4:$F$10,0,MATCH(B$1,Weave!$B$3
    > :$F$3,0)))
    >
    > Note that if you continue to add the dates along the first row of your
    > summary sheet, the formula can be copied across to other columns giving
    > you a summary for each date.
    >
    > Hope this helps!
    >
    > In article <06C49AE4-761A-4AB6-A629-9B1EBA66618A@microsoft.com>,
    > "CheeseHeadTransplant"
    > <CheeseHeadTransplant@discussions.microsoft.com> wrote:
    >
    > > I have a spreadsheet with 3 sheets of date. On all 3 column A is the same
    > > and Row 3 is the same. Column A is labeled Weave_Market_MTD and Row 3 is
    > > Weave_Date_MTD. I need to sum all like items in Column A for a Date I have
    > > in Cell D7 of the summary page. Before changing this sheet Column A did not
    > > have two cells the same so I used the formula
    > >
    > > =INDEX(Weave!$1:$65536,MATCH($A15,Weave!A:$A,0),MATCH($D$7,Weave!$3:$3,0))
    > >
    > > Now I have to add all the items for the same Market. This is what my sheet
    > > looks like now:
    > > 09/01/05 09/02/05 09/03/05 09/04/05
    > > 09/05/05
    > > C/R 5 10 15
    > > 20 25
    > > EIFS 5 10 15
    > > 20 25
    > > Export 5 10 15
    > > 20 25
    > > OEM 5 10 15
    > > 20 25
    > > Export 5 10 15
    > > 20 25
    > > Export 5 10 15
    > > 20 25
    > > OEM 5 10 15
    > > 20 25
    > >
    > > If I need the total for 09/03/05 I need C/R = 15, EIFS = 15, OEM = 30 and
    > > Export = 45.

    >


  8. #8
    Domenic
    Guest

    Re: Sum cells based on a row variable and seperate column variable

    Can you post the exact formula you're using?

    In article <CD35012F-4002-4490-8757-A07A16CE0EB1@microsoft.com>,
    "CheeseHeadTransplant"
    <CheeseHeadTransplant@discussions.microsoft.com> wrote:

    > This seems to be working except for one small problem... it is giving the
    > results for the wrong day. It is always off 3 days. If I query 09/01/05 it
    > is giving the results for 09/04/05. I can't figure out why the strange
    > result.


  9. #9
    CheeseHeadTransplant
    Guest

    Re: Sum cells based on a row variable and seperate column variable

    Sorry... guess that would help....

    =SUMIF(Weave_Market_MTD,A13,INDEX(Weave_Data_MTD,0,MATCH(Date,Weave_Date_MTD,0)))

    where:

    Weave_Market_MTD is on the sheet "Weave" and is column A

    A13 is the item I need to sumif (C/R, EIFS, Scrim, OEM etc.)

    Weave_Data_MTD is on the sheet "Weave" and is all the daily totals for each
    market.

    Date is the date the formula is to use to find the column to sum

    Weave_Date_MTD is on the sheet "Weave" and is row 3 with a date and all data
    entered for that day below it.

    "Domenic" wrote:

    > Can you post the exact formula you're using?
    >
    > In article <CD35012F-4002-4490-8757-A07A16CE0EB1@microsoft.com>,
    > "CheeseHeadTransplant"
    > <CheeseHeadTransplant@discussions.microsoft.com> wrote:
    >
    > > This seems to be working except for one small problem... it is giving the
    > > results for the wrong day. It is always off 3 days. If I query 09/01/05 it
    > > is giving the results for 09/04/05. I can't figure out why the strange
    > > result.

    >


  10. #10
    Domenic
    Guest

    Re: Sum cells based on a row variable and seperate column variable

    It's difficult to see what's going on without the actual cell references.

    Nevertheless, make sure that your named ranges Weave_Data_MTD and
    Weave_Date_MTD reference the same range of columns.

    If you still have problems, re-post the formula with the actual cell
    references.

    In article <842F9230-7773-48BD-A38F-FAE383D0E86B@microsoft.com>,
    "CheeseHeadTransplant"
    <CheeseHeadTransplant@discussions.microsoft.com> wrote:

    > Sorry... guess that would help....
    >
    > =SUMIF(Weave_Market_MTD,A13,INDEX(Weave_Data_MTD,0,MATCH(Date,Weave_Date_MTD,0
    > )))
    >
    > where:
    >
    > Weave_Market_MTD is on the sheet "Weave" and is column A
    >
    > A13 is the item I need to sumif (C/R, EIFS, Scrim, OEM etc.)
    >
    > Weave_Data_MTD is on the sheet "Weave" and is all the daily totals for each
    > market.
    >
    > Date is the date the formula is to use to find the column to sum
    >
    > Weave_Date_MTD is on the sheet "Weave" and is row 3 with a date and all data
    > entered for that day below it.


  11. #11
    Deeds
    Guest

    Re: Sum cells based on a row variable and seperate column variable

    Thanks! It works great!

    "Domenic" wrote:

    > See your other post...
    >
    > In article <A85682BF-ED88-4DFE-BBAF-844CC5CC04D8@microsoft.com>,
    > "Deeds" <Deeds@discussions.microsoft.com> wrote:
    >
    > > Can you go to my post Vlookup "crosstab"....you will find an example there.
    > > I just posted it....thanks!

    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1