+ Reply to Thread
Results 1 to 11 of 11

Problem with SUMIFS

  1. #1
    Forum Contributor
    Join Date
    09-18-2014
    Location
    England
    MS-Off Ver
    2003/2010/365
    Posts
    119

    Lightbulb Problem with SUMIFS

    Hello,

    Could you please help me correct the below:

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


    Desired outcome:
    Sum if this is criteria is met:
    1. Left three characters of column F = 7399
    2. Right three characters of B:B are equal to Z10 (Z10 will change to follow column Z has formula is dragged down)

    Thanks in advance

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Problem with SUMIFS

    Hi,

    a first attempt

    Please Login or Register  to view this content.

    Cheers



    Edit: maybe ="7399"
    Last edited by canapone; 10-24-2014 at 07:29 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Problem with SUMIFS

    hI, tRY

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Boo?ath?

  4. #4
    Forum Contributor
    Join Date
    09-18-2014
    Location
    England
    MS-Off Ver
    2003/2010/365
    Posts
    119

    Re: Problem with SUMIFS

    Didn't work

  5. #5
    Forum Contributor
    Join Date
    09-18-2014
    Location
    England
    MS-Off Ver
    2003/2010/365
    Posts
    119

    Re: Problem with SUMIFS

    If it helps; for the second criteria I can change RIGHT(B:B,3)=Z10 to "If Z10 is contained in corresponding row in B:B".

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Problem with SUMIFS

    Quote Originally Posted by SharpL View Post
    Sum if this is criteria is met:
    1. Left three characters of column F = 7399
    2. Right three characters of B:B are equal to Z10 (Z10 will change to follow column Z has formula is dragged down)
    Do you mean left four characters for 1?

    What sort of data is in column F and B, are they completely numeric, alphanumeric or something else?
    Audere est facere

  7. #7
    Forum Contributor
    Join Date
    09-18-2014
    Location
    England
    MS-Off Ver
    2003/2010/365
    Posts
    119

    Re: Problem with SUMIFS

    Sorry, yes 4.

    Column example:

    B - 23/10/14 23/10/14 25 T36
    F - 9.31

  8. #8
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Problem with SUMIFS

    hi, could u post a sample in excel

  9. #9
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Problem with SUMIFS

    Hi,

    a sample file could be helpful

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Problem with SUMIFS

    OK,

    The problem, potentially is that you can't use wildcards in SUMIFS with numeric data, e.g. using "7399*" as one of the criteria would work if the values were zzz7399 or 123xyz7399 but it won't work for numbers like 7399.45 or 739989.

    When there's a match for 7399 what values are possible, could it be 7399123456 or is 7399 always going to be the part before the decimal point?

  11. #11
    Forum Contributor
    Join Date
    09-18-2014
    Location
    England
    MS-Off Ver
    2003/2010/365
    Posts
    119

    Re: Problem with SUMIFS

    7399 will always be the first four characters

+ 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. [SOLVED] Sumifs problem
    By hammer2 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-20-2014, 03:21 AM
  2. Sumifs problem
    By kim296 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-02-2013, 02:56 PM
  3. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  4. SUMIFS Problem
    By denise001 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-12-2011, 08:38 AM
  5. SUMIFS problem
    By K Thomas in forum Excel General
    Replies: 3
    Last Post: 04-19-2011, 08:23 AM

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