+ Reply to Thread
Results 1 to 8 of 8

Sumif 2 criterias one horizontal with duplicates and 1 vertical

  1. #1
    Registered User
    Join Date
    07-06-2010
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    98

    Sumif 2 criterias one horizontal with duplicates and 1 vertical

    Hi there,

    I have a table with values. On the y axis i have 3 types (A, B and C). On the x-axis I have calendar weeks (42, 43, 44, 45, 46, 47, 48 and 49). I know how to do a SUMIF for this task. However in my table every calendar week occurs 7 times.

    I need to sum all values in the table according to the 3 individual types and 8 individual calendar weeks.

    attaches is an excel file which shows the table in sheet1 and the desired output in sheet 2.

    The location of the cells must not be changed. Ideally I do not want to use a macro.

    Thanks in advance for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Sumif 2 criterias one horizontal with duplicates and 1 vertical

    SUMIF doesn't work with multiple columns in the sum range, only the criteria range.

    There are probably workarounds with OFFSET and INDIRECT but SUMPRODUCT would be easier to implement.

    =SUMPRODUCT((Sheet1!$F$6:$F$156=Sheet2!$A2)*(Sheet1!$L$1:$BO$1=Sheet2!B$1)*Sheet1!$L$6:$BO$156)

  3. #3
    Registered User
    Join Date
    10-07-2015
    Location
    Vancouver, BC, Canada
    MS-Off Ver
    Office 365
    Posts
    22

    Re: Sumif 2 criterias one horizontal with duplicates and 1 vertical

    please find the attached file

    pay attention to yellow cells
    i change 7 column for each week to one week. then i use sumif
    any question?

    Feel free to follow my blog, http://msexcelvancouver.com/blog/
    Last edited by bsuperiorsystem; 10-27-2015 at 01:25 AM.

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Sumif 2 criterias one horizontal with duplicates and 1 vertical

    Quote Originally Posted by bsuperiorsystem View Post
    i change 7 column for each week to one week. then i use sumif
    any question?
    Why use over 1200 formula when you only need 24?

    One example in post #2, and another below using your method of sumif with volatile functions that doesn't need those 1200 extra formula.

    =SUMPRODUCT(SUMIF(Sheet1!$F$6:$F$156,Sheet2!$A2,OFFSET(Sheet1!$K$6:$K$156,0,ROW(OFFSET($A$1,MATCH(Sheet2!B$1,Sheet1!$L$1:$BO$1,0)-1,0,COUNTIF(Sheet1!$L$1:$BO$1,Sheet2!B$1))))))

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,711

    Re: Sumif 2 criterias one horizontal with duplicates and 1 vertical

    removed -----------
    Last edited by samba_ravi; 10-26-2015 at 07:57 AM.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Sumif 2 criterias one horizontal with duplicates and 1 vertical

    @nflsales

    Normally I would agree with you there, but I couldn't see any way to replace the first offset with index (or any other non volatile method).
    The second offset could be replace with index, but this would increase the number of functions needed in the formula, as it was already volatile with the first offset, I didn't see any point in over-engineering the formula to remove the second one.

  7. #7
    Registered User
    Join Date
    07-06-2010
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: Sumif 2 criterias one horizontal with duplicates and 1 vertical

    guys,

    thanks for the effort. I will try your solution later. If they work I will close the threat and add some reputation to your profiles.

  8. #8
    Registered User
    Join Date
    07-06-2010
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: Sumif 2 criterias one horizontal with duplicates and 1 vertical

    Worked! Thanks so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sumif Formula With 2 Condition in Horizontal & Vertical
    By Pawan Thakur in forum Excel General
    Replies: 4
    Last Post: 04-27-2015, 09:11 AM
  2. Replies: 14
    Last Post: 07-12-2012, 01:32 AM
  3. Excel 2007 : Horizontal to vertical
    By godfredafrifa in forum Excel General
    Replies: 1
    Last Post: 11-06-2011, 11:54 AM
  4. Horizontal to vertical
    By godfredafrifa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2011, 10:18 AM
  5. Horizontal to vertical
    By aaslezak in forum Excel General
    Replies: 4
    Last Post: 03-30-2009, 11:47 AM
  6. Vertical to Horizontal
    By billykiller05 in forum Excel General
    Replies: 3
    Last Post: 12-06-2007, 12:04 PM
  7. Horizontal to Vertical
    By Jett in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 10-04-2007, 03:37 PM

Tags for this Thread

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