+ Reply to Thread
Results 1 to 5 of 5

sumifs with extra columns

  1. #1
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    669

    sumifs with extra columns

    Hi guys,

    I am trying to sum a few columns together with a criteria. Can someone please offer guidance on the formula I should use please?

    my sheet attachd.

    I want a formula in cell IC26 please to add cells from the holiday payment column (column IC) if the column HR to pay? (column IG) has yes inserted. Another caveat.. for test 11 (row 13), I need to add use IJ13 instead of IC13... so values in column IJ should prioritise column IC.

    Can someone help please/


    thanks!
    Attached Files Attached Files
    Thanks,

    R.



  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,990

    Re: sumifs with extra columns

    MAy be:

    Please Login or Register  to view this content.

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,304

    Re: sumifs with extra columns

    you certainly don't need the array formula you have in IC27, this simple sumif formula will give you the same result... =SUMIF(IG3:IG18,"yes",IC3:IC18)

    What I'm not quite understanding is, are you saying if a value exists anywhere in column IJ it should override the value in IC?
    if so, perhaps you could add that condition to your index formula in column IC from IC3 to IC18?
    like this...
    =IF(IJ13<>"",IJ13,INDEX(March 2021'!$L$2:$L$29,MATCH('HR '!$A13,March 2021'!$A$2:$A$25,0)))
    I had to remove the additional paths since I cannot access them, but maybe that will be an option for you.
    Last edited by Sam Capricci; 04-20-2021 at 06:42 AM. Reason: changed AJ to IJ, incorrectly pointed formula
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,021

    Re: sumifs with extra columns

    Maybe try

    =SUMIFS(IC3:IC20,IC3:IC20, "<>#N/A",$IG$3:$IG$20,"yes",IJ3:IJ20,"=")+SUMIFS(IJ3:IJ20,IG3:IG20,"Yes")
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    669

    Re: sumifs with extra columns

    THANK YOU ALL! Rep added. The solutions you gave work :-)

+ 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. SUMIFS return duplicates- extra condition?
    By Shruder in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-14-2018, 01:58 PM
  2. Replies: 4
    Last Post: 01-30-2017, 07:16 PM
  3. How to delete the extra columns you don't need?
    By mainline10925 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-21-2012, 03:48 PM
  4. Replies: 8
    Last Post: 08-27-2012, 04:37 PM
  5. Extra 'F' columns
    By robbath in forum Excel General
    Replies: 2
    Last Post: 06-08-2009, 08:31 AM
  6. Excel 2007 extra columns - but transposing columns still not working!
    By garyusenet@myway.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-26-2006, 10:00 AM
  7. Extra Columns
    By Peco Worker in forum Excel General
    Replies: 3
    Last Post: 05-23-2005, 09:06 AM

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