+ Reply to Thread
Results 1 to 13 of 13

sumifs with mulitple columns and mulitple criteria in each column

  1. #1
    Registered User
    Join Date
    07-18-2012
    Location
    Little Rock, AR
    MS-Off Ver
    Excel 2010
    Posts
    13

    sumifs with mulitple columns and mulitple criteria in each column

    I am having trouble writing a function in excel that will count the number (similar to countifs function) and sum the $ amount (like the sumifs function, but that was not working due to the multiple columns with multiple criteria options in each column).

    Example:

    Sum Column R, IF

    Column AB = 1C1 or 1C2A or 1C2B or 1A1 or 1D AND
    Column Z = 128 or 160 or 650 or 125 or 641 AND
    Column X = 1 or 2 or 3 or 5 or 6 or 7 or 10 or 12 AND
    Columns AN = 005 or 012 AND
    Column O = <2011000

    Please advise if anyone knows of a function. This is my first post, so please let me know if more detail is required.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sumifs with mulitple columns and mulitple criteria in each column

    Try:

    Please Login or Register  to view this content.
    where column A:A is column to sum up.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    07-18-2012
    Location
    Little Rock, AR
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: sumifs with mulitple columns and mulitple criteria in each column

    It returned a "#value" is there a quick fix, this is the first time I have not gotten an error message so that is progress. Thank you.

    ---------- Post added at 12:42 PM ---------- Previous post was at 12:42 PM ----------

    It returned a "#value" is there a quick fix for that?

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sumifs with mulitple columns and mulitple criteria in each column

    What is the actual formula you used? Are ranges all same size?

  5. #5
    Registered User
    Join Date
    07-18-2012
    Location
    Little Rock, AR
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: sumifs with mulitple columns and mulitple criteria in each column

    =SUM(SUMIFS(AllLoans!R2:R5223,AllLoans!AB2:AB5223,{"1C1","1C2A","1C2B","1A1","1D"},AllLoans!Z2:Z5223,{128,160,650,125,641},AllLoans!X2:X5223,{1,2,3,5,6,7,10,12},AllLoans!AN2:AN5223,{"005","012"},AllLoans!O2:O5223,"<2011000"))

    I actually noted that I had typed 5233 in one of the columns, so now it is just returning "0", but I know there should be a some that work. These are "OR" criteria correct?

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sumifs with mulitple columns and mulitple criteria in each column

    If these columns: AllLoans!Z2:Z5223 and/or AllLoans!X2:X5223 are text values, then put quotes around the entries in corresponding arrays: {128,160,650,125,641} and or {1,2,3,5,6,7,10,12} in the formula...

  7. #7
    Registered User
    Join Date
    07-18-2012
    Location
    Little Rock, AR
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: sumifs with mulitple columns and mulitple criteria in each column

    I tried that and it did not work. Is there a way to make sure there are not any hidden spaces after the text in the columns that are prohibiting a return value.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sumifs with mulitple columns and mulitple criteria in each column

    you would have to clean them.. perhaps using FIND/REPLACE replacing " " with "" (i.e. space with null)

  9. #9
    Registered User
    Join Date
    07-18-2012
    Location
    Little Rock, AR
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: sumifs with mulitple columns and mulitple criteria in each column

    It is still returning 0. Any other thoughts? I really appreciate you taking the time to think through this.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sumifs with mulitple columns and mulitple criteria in each column

    Post the workbook...

  11. #11
    Registered User
    Join Date
    07-18-2012
    Location
    Little Rock, AR
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: sumifs with mulitple columns and mulitple criteria in each column

    sample.xlsx

    I had to alter the data due to sensitive information, but the sample should provide the idea. I have updated the formula on the summary tab.

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sumifs with mulitple columns and mulitple criteria in each column

    Apparently I might have messed that up... try instead...

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    07-18-2012
    Location
    Little Rock, AR
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: sumifs with mulitple columns and mulitple criteria in each column

    That worked on the sample sheet. I will apply to the original workbook. Thank you so much for your help. I really appreciate it.

+ 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