+ Reply to Thread
Results 1 to 4 of 4

Sumif with criteria for Row and Column

  1. #1
    Registered User
    Join Date
    01-04-2010
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    26

    Sumif with criteria for Row and Column

    Hi,
    Please help me out with the Solution to this problem..
    The problem at hand is i need to add Data from two different tables as:

    Sales(Jan) Sales(Feb) Sales(March)
    Product1 23 12 11
    Product2 11 8 9
    Product3 10 6 10

    Sales(Jan) Sales(March)
    Product1 23 11
    Product3 11 9
    Product4 10 10


    As you can see,We need to get the data into a new table in the form of:

    Sales(Jan) Sales(Feb) Sales(March)
    Product1 46 12 22
    Product2 11 8 9
    Product3 21 6 19
    Product4 10 0 10

    Basically i need to Just add the Data from Both tables whose Row and Column coefficients are Same.

    That is,For Product 1, i need to add Sales in Jan,Feb and Mar from Table 1,but i can only add sales for Jan and Mar,but not for Feb from Table2....(Hence dynamically take relevant data from both tables..)

    Hence similarly for Product2,its not present in table2,hence i need to show only data from table1 for it thats relevant..Please clarify my doubts regarding this..


    I hope i am clear.! ...


    Thanks a lot..
    Last edited by gigiw1986; 02-19-2010 at 02:12 AM.

  2. #2
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Sumif with crietrion for Row and Column

    Actually it can be done with formula but better to do this with pivot. See example if it meets your requirments
    Attached Files Attached Files
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, «Born in USSR»
    Vusal M Dadashev

    Baku, Azerbaijan

  3. #3
    Registered User
    Join Date
    01-04-2010
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Sumif with criteria for Row and Column

    Hi..
    thanks a lot for your response.
    but the issue is the 2 tables 1 and 2 i was talking about wee themselves Pivot tables.
    So can u pelase tell em another way out
    but i sincerely appreiciate your response
    Thanksa lot for this.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Sumif with criteria for Row and Column

    If the tables are Pivots I would suggest you check out the GETPIVOTDATA function.

    Use the above in conjunction with IFERROR to handle instances whereby a given combination does not exist in the Pivot, eg:

    =IFERROR(GETPIVOTDATA(...),0)+IFERROR(GETPIVOTDATA(...),0)

    where the first GETPIVOTDATA call is pointed at first PT and second at second PT

    I think you will find the above approach pretty simple to setup.


    If you need further assistance I would strongly advise posting a sample file that reflects your setup and desired output (dummy values obviously if confidential).

+ 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