+ Reply to Thread
Results 1 to 6 of 6

Sum based on horizontal and vertical match

  1. #1
    Registered User
    Join Date
    03-28-2007
    Posts
    71

    Sum based on horizontal and vertical match

    Okay. I am trying to get a sum based using the top row headers and the answer in column A. Originally this was an easy index match sort of thing, but then my report grew and now I need the sum of these.

    I am attaching a very simple randomly inputted spreadsheet to give the example. So, using the example. The "Data sheet" has animals listed in Column A and date headers in B1:C1. On the results sheet in B2 I need the sum of every time the result of A2 and B1 meet on the Data sheet. I have NO idea how to do this.

    Please keep in mind, while this seems extremely simple on this sheet, my actual worksheet is pretty huge and constantly changing, so I don't want to have to assign columns for a normal sumif.

    Thanks in advance for your help.

    Test Worksheet.xlsx

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Sum based on horizontal and vertical match

    Seems like you can just do an easy Sumif.

    in B2 enter =SUMIF(Data!$A$2:$A$13,Results!$A2,Data!B$2:B$13) and drag across and down

  3. #3
    Registered User
    Join Date
    03-28-2007
    Posts
    71

    Re: Sum based on horizontal and vertical match

    The only problem with that is the headers don't match as easily as I made it seem. And they aren't consistent. So, while I currently need 5/1/13 the data sheet may actually start with 6/1/11. So, it's not an easy column match.

  4. #4
    Registered User
    Join Date
    03-28-2007
    Posts
    71

    Re: Sum based on horizontal and vertical match

    This is probably actually a better representation of my spreadsheet.

    Test Worksheet.xlsx <--this one.
    Attached Files Attached Files
    Last edited by Prcntrygrl; 03-29-2013 at 01:29 PM.

  5. #5
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Sum based on horizontal and vertical match

    Hi Prcntrygrl

    Try the sumproduct:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In B2 copy across & down.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  6. #6
    Registered User
    Join Date
    03-28-2007
    Posts
    71

    Re: Sum based on horizontal and vertical match

    I love when it's so much simpler than I make it! Thank you SOOO much!!!

+ 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