+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Nested SUMIFs or ??

  1. #1
    Registered User
    Join Date
    11-03-2010
    Location
    Dallas, Tx
    MS-Off Ver
    Excel 2007
    Posts
    36

    Nested SUMIFs or ??

    I need a formula that will search worksheet 1 row 1 for an ID matching worksheet 2 row 1 and return the sum of all data when worksheet 1 col A matches a set character.

    Basically I need a sum if that will search for 2 specific criteria in two specific areas.

    Example attached.

    This is the formula I have in it now, returning a #Value error.

    =SUM(IF(WTB!S12:JQ12, B2, IF(WTB!I14:I133, "A105",WTB!S14:JE135)))

    B2 being the ID I need it to search for, "A105" is the identifier I need it to sum.

    These are some of the samples of formulas I am using in other areas of this project.

    =IFERROR(VLOOKUP($B14,INDIRECT("'"&SUBSTITUTE(VLOOKUP(BT$12,Master!$A$1:$D$999,2,FALSE),"'","''")&" "&BT$12&"'!$A:$D"),4,FALSE),"")
    =SUMIF($L$12:$ZY$12, $A$2, L78:ZY78)
    =INDEX('PS&R Data'!$C$2:$ZZ$213, MATCH(D11, 'PS&R Data'!$B2:$B$163, 0), MATCH(E2, 'PS&R Data'!$C$2:$ZZ$2, 0))

    The only one I thought might work is the INDEX but I don't have an unique identifier for each of the 150 clients data.

    My guess (and hope) this one won't be so complicated and I am just WAY over-thinking it!!

    Any help would be appreciated!

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Nested SUMIFs or ??

    Using your posted workbook, here's the approach I used...
    • Test sheet range I4:J9 contains this lookup list:
    Please Login or Register  to view this content.
    Now, this regular formula returns the total you're looking for
    Please Login or Register  to view this content.
    Copy that formula across and down through G7.

    Is that something you can work with?
    Attached Files Attached Files
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    11-03-2010
    Location
    Dallas, Tx
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: Nested SUMIFs or ??

    I need something that doesn't require a search list. The only reason I included it was because my "Totals" don't match my search criteria.

    In reality I am searching for "Total Admin Wages" which fall on form line A105. So I need to search for my clients id then search all their financials to return the sum of everything referencing line A105.

    Is that possible?

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Nested SUMIFs or ??

    It might be possible, but we need more detailed information about the documents.
    Ideally, the financials would be structured uniformly. That would facilitate the finding of specific text and the totalling of corresponding amounts.

  5. #5
    Registered User
    Join Date
    11-03-2010
    Location
    Dallas, Tx
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: Nested SUMIFs or ??

    I have 150+ clients who I am doing MC reports on. I am taking their Trial Balances (TB) and condensing them based on MC requirements. I am then taking the condensed TB and adding them to a Master list. The master list is in order of completion NOT order of ID.

    I then have a comparison worksheet that will pull specific data like Total Admin and compare one client to all 150+ others. My comparison is in client ID order.

    NOW some of my report line references are only used once, some are used 10 times.

    I have to search my master for the corresponding ID based on my comparison and return the sum of whatever report line reference I need.

    For example: All my admin related expenses will fall on report line reference A105. But I have 20 different accounts that will fall under this reference so I need to sum all of them to return my "Total Admin" on my comparison.

    I have attached a slightly varied example with more detailed names of accts/columns/etc.

    Thanks
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-03-2010
    Location
    Dallas, Tx
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: Nested SUMIFs or ??

    Thanks but I figured out a way to make it work.

+ 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