+ Reply to Thread
Results 1 to 13 of 13

Countif with Sumif is producing a single error in output

  1. #1
    Forum Contributor
    Join Date
    06-10-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    272

    Countif with Sumif is producing a single error in output

    Hello,

    I have the following formula copied down 300 rows, when it encounters the first empty row, it returns a 0 instead of the expected blank. this is the only time this occurs over 300 rows.
    This is causing me issues in other sheets that refer to these cells.

    =IF(COUNTIF($A$2:A5,A5)=1,SUMIF($A$2:$A$30,A5,$B$2:$B$30),"")

    I've attached a small sample sheet to illustrate the problem.
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Countif with Sumif is producing a single error in output

    The result of the countif actually IS 1, so the IF is TRUE.

    No matter what value is in A5 (except error values), the countif will always be at least 1.
    Because A5 is included in the range it's testing.
    So the value (or non-value) of A5 does indeed exist within the range of A2:A5, so the count is 1.

    Try this way in G2
    =IF(OR(A2="",A2=A1),"",SUMIF($A$2:$A$30,A2,$B$2:$B$30))

  3. #3
    Forum Contributor
    Join Date
    06-10-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    272

    Re: Countif with Sumif is producing a single error in output

    Thank you for your help Jonmo,

    Brilliant, solved the issue.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Countif with Sumif is producing a single error in output

    reason is that infact a5 is not truly blank try =isblank(a5) it returns false so =COUNTIF(A5:A5,A5) as a tests returns 1
    if it was truly blank it would return 0, it depends where you sourced the data from
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Forum Contributor
    Join Date
    06-10-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    272

    Re: Countif with Sumif is producing a single error in output

    Martin, thanks for your reply.

    A5 is made blank as the result of a formula, if not blank then a date is displayed.
    Could you offer another solution ?

    Also, some of the dates are repeated down the column with associated values in col B.
    If at all possible I would like the total value of all occurances in Col B associated with a particular date to be shown only at the first occurance of that date.

    So the first time a date is displayed in Col A, a total should appear in Col G on the same row.

    Any help you can offer would be great

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Countif with Sumif is producing a single error in output

    not in your example workbook it isnt,there is no formula in a5

  7. #7
    Forum Contributor
    Join Date
    06-10-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    272

    Re: Countif with Sumif is producing a single error in output

    This is the real formula that appears in my worksheet in what would correspond to A5, but is actually N18. G2 would be T18

    =IF(OR($C$3<$D18,$O$1<$D$17),"",$J18+N$12)

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Countif with Sumif is producing a single error in output

    Doesn't the formula from Post #2 still solve the issue, whether A5 is truely blank or the result of a formula blank?

  9. #9
    Forum Contributor
    Join Date
    06-10-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    272

    Re: Countif with Sumif is producing a single error in output

    Hi Jonmo,

    Yes and no I'm afraid.
    My original formula produced a total for all quantities for all occurrences of a particular date, at the first occurrence of that date.

    Your formula does a total AT each occurrence of the same date, so multiplies up the total

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Countif with Sumif is producing a single error in output

    I see, hang on...
    Attached Files Attached Files

  11. #11
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Countif with Sumif is producing a single error in output

    Try

    =IF(OR(A2="",COUNTIF($A$2:A2,A2)>1),"",SUMIF($A$2:$A$30,A2,$B$2:$B$30))

  12. #12
    Forum Contributor
    Join Date
    06-10-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    272

    Re: Countif with Sumif is producing a single error in output

    Hi Jonmo,

    Thanks for your continued efforts.

    It looks like your latest formula is working well. I'll do some more extensive testing tomorrow and post the result, but at the moment it's looking good.

    thanks again for your help.

  13. #13
    Forum Contributor
    Join Date
    06-10-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    272

    Re: Countif with Sumif is producing a single error in output

    Hey Jonmo,

    Your revised formula continues to work well, producing the expected results without errors.
    Thank you once again for helping me.
    regards

+ 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] Syntax producing an error
    By paradise2sr in forum Excel General
    Replies: 7
    Last Post: 05-25-2014, 12:13 PM
  2. loop that keeps producing a next without for error
    By Sc0tt1e in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 03-26-2014, 04:49 AM
  3. [SOLVED] Paste variable output (SQL Output) into a single cell.
    By fblaze in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-28-2013, 06:24 AM
  4. Excel 2007 : Countif producing zero
    By markDuffy in forum Excel General
    Replies: 11
    Last Post: 04-26-2011, 12:33 PM
  5. [SOLVED] countif / sumif function error
    By Mark J. in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-04-2006, 05:55 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