+ Reply to Thread
Results 1 to 13 of 13

SUMIF producing unexpected zero results

  1. #1
    Registered User
    Join Date
    04-29-2019
    Location
    Senekal, South Africa
    MS-Off Ver
    Professional plus 2016
    Posts
    3

    SUMIF producing unexpected zero results

    Good day

    I am hoping someone can help me I am struggling with SUMIF formulas on a specific worksheet. Basically my sheet has a column with cellphone numbers in and another with the duration of the calls. I want to calculate the total call duration of a specific number for the period. So what i have tried is a basic sumif formula but the results stay zero. If I type in the formula as indicated on various websites and this forum i get an error message regarding the apostrophe. If i do a sumproduct formula I get an Value error. The sumif formula i am using is =SUMIF(C:C,"S1",H:H), if i use the same formula without the "" then the answer comes back as zero which is wrong.

    Any ideas
    Thanks
    William
    Last edited by AliGW; 04-29-2019 at 08:19 AM.

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,798

    Re: SUMIF problems

    Your formula looks correct assuming column C has the text S1 in it. Hard to be more helpful without being able to see the data...
    Rory
    I drink, and I know things

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

    Re: SUMIF problems

    Hi WilliamRSA,

    an attempt

    =SUMPRODUCT((C3:C1000=S1)*(H3:H1000+0))

    Formula must elaborate in H3:H1000 only time durations: no text strings.

    Again, a sample file would be helpful.

    Regards
    -----------------------------------------------------

    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.

  4. #4
    Registered User
    Join Date
    04-29-2019
    Location
    Senekal, South Africa
    MS-Off Ver
    Professional plus 2016
    Posts
    3

    Re: SUMIF problems

    Thanks for the reply Rorya

    Attached is a sample of my data with two sumif formulas i have tried as well as a sumproduct formula

    SUMIf.xlsx

    Hope the attachement comes out alright

    Regards

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,865

    Re: SUMIF producing unexpected zero results

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As you are new here, I have done it for you this time.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

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

    Re: SUMIF producing unexpected zero results

    Hi again

    (...)

    Sorry AliGW...I was slowly writing...

  7. #7
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,880

    Re: SUMIF producing unexpected zero results

    SUMPRODUCT does not work with entire ranges. replace them with something like B2:B100 ( they must be the same length, though)
    Your col B consists of text looking like numbers, not real numbers ( left aligned). To correct, select col C, Data - Text to columns - Finish ( should now be right aligned)

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,798

    Re: SUMIF producing unexpected zero results

    Quote Originally Posted by Pepe Le Mokko View Post
    SUMPRODUCT does not work with entire ranges.
    It does actually (I'm assuming you meant columns, not ranges). Not very efficient here though.

  9. #9
    Registered User
    Join Date
    04-29-2019
    Location
    Senekal, South Africa
    MS-Off Ver
    Professional plus 2016
    Posts
    3

    Re: SUMIF producing unexpected zero results

    Thanks Pepe

    That seems to have fixed it, thank you all for the amazingly quick feedback and help, and a great forum

    Kind regards

  10. #10
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,880

    Re: SUMIF producing unexpected zero results

    Quote Originally Posted by rorya View Post
    It does actually (I'm assuming you meant columns, not ranges).
    Correct. I wonder where I got that idea...

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,865

    Re: SUMIF producing unexpected zero results

    Quote Originally Posted by canapone View Post
    Hi again

    (...)

    Sorry AliGW...I was slowly writing...
    No need to wait. See the last line of my message.

  12. #12
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,880

    Re: SUMIF producing unexpected zero results

    Quote Originally Posted by WilliamRSA View Post
    Thanks Pepe
    Most welcome

  13. #13
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,798

    Re: SUMIF producing unexpected zero results

    Quote Originally Posted by Pepe Le Mokko View Post
    Correct. I wonder where I got that idea...
    There used to be an issue with array formulas and entire columns in 2003 and earlier.

+ 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. Excel 2007 : SUMIF function (problems)
    By alba123 in forum Excel General
    Replies: 5
    Last Post: 12-18-2008, 09:46 AM
  2. SUMIF problems
    By garethgtt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2007, 10:35 PM
  3. Re: sumif problems
    By vezerid in forum Excel General
    Replies: 3
    Last Post: 03-30-2006, 05:10 PM
  4. [SOLVED] Problems with SUMIF()
    By Navision in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-03-2006, 12:25 PM
  5. problems with sumif and countif
    By Simon Shaw in forum Excel General
    Replies: 6
    Last Post: 07-23-2005, 05:05 PM
  6. [SOLVED] using Sumif Problems
    By Anat in forum Excel General
    Replies: 6
    Last Post: 06-16-2005, 02:05 AM
  7. SUMIF Problems
    By lkw441 in forum Excel General
    Replies: 3
    Last Post: 03-21-2005, 04:53 PM
  8. Sumif Problems
    By cybercab in forum Excel General
    Replies: 1
    Last Post: 02-05-2005, 07:31 PM

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