+ Reply to Thread
Results 1 to 36 of 36

"Countif" with "left" error, "Sumproduct" picking up not qualifying rows

  1. #1
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    Excel 2002
    Posts
    595

    "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Hi

    Can someone tell me what is wrong with the COUNTIF and why SUMPRODUCT picks up those 2 rows?

    edit: I meant rows 10 and 31 (not 32)

    Note: entries in col A are entered as text.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by drgkt; 01-09-2016 at 06:39 PM.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,964

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Hi,

    This is confusing. Why do you mention rows 10 & 31 when column C doesn't have a number anyway

    Please confirm what results you do expect.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Try this...
    =COUNTIF(A10:A31,"302*")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    THis gives you 8...
    =SUMPRODUCT(--(C10:C31>0),--(LEFT(A10:A31,3)<>"302"),--(LEFT(A10:A31,3)<>"306"),--(LEFT(A10:A31,1)<>"2"),--(LEFT(A10:A31,1)<>"6"))

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Try selecting C10 and C31 and hitting the delete key. SUMPRODUCT will return 6. There is text in those cells and ("text" >0) = TRUE.
    Dave

  6. #6
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    Excel 2002
    Posts
    595

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by FDibbins View Post
    Try this...
    =COUNTIF(A10:A31,"302*")
    Does not work on office 2002

  7. #7
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    Excel 2002
    Posts
    595

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by FDibbins View Post
    THis gives you 8...
    =SUMPRODUCT(--(C10:C31>0),--(LEFT(A10:A31,3)<>"302"),--(LEFT(A10:A31,3)<>"306"),--(LEFT(A10:A31,1)<>"2"),--(LEFT(A10:A31,1)<>"6"))
    Yes, but 8 is wrong!

  8. #8
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    Excel 2002
    Posts
    595

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by FlameRetired View Post
    Try selecting C10 and C31 and hitting the delete key. SUMPRODUCT will return 6. There is text in those cells and ("text" >0) = TRUE.
    Bummer! text should be >"" not >0 !!! ( I will Complain to MS !!!)

    I d like to exclude them somehow without removing them...

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by drgkt View Post
    Does not work on office 2002
    sorry, should have read your profile better

  10. #10
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    Excel 2002
    Posts
    595

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    As far as why the COUNTIF does not work, it remains a mystery...

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by drgkt View Post
    Yes, but 8 is wrong!
    I dont have the summary now (deleted it), but I can make it again - it showed that 8 was correct. My test summery was a bunch of helper columns that progressively applied your criteria

    Why is 8 wrong and what do you think it should be?

  12. #12
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    Excel 2002
    Posts
    595

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by FDibbins View Post
    I dont have the summary now (deleted it), but I can make it again - it showed that 8 was correct. My test summery was a bunch of helper columns that progressively applied your criteria

    Why is 8 wrong and what do you think it should be?
    It should be 6, just count them on the picture.

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    OK, I did that yesterday, memory must be failing lol. Try this...

    =SUMPRODUCT(--ISNUMBER(C10:C31),--(C10:C31>0),--(LEFT(A10:A31,3)<>"302"),--(LEFT(A10:A31,3)<>"306"),--(LEFT(A10:A31,1)<>"2"),--(LEFT(A10:A31,1)<>"6"))

    It would be simpler if you excluded rows 10 and 31

  14. #14
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    Excel 2002
    Posts
    595

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    ok. That did it! Thanks.

    Can you tell me why the COUNTIF returns 0?

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    to use COUNTIF()...
    =COUNTIF(A10:A31,"302*")

  16. #16
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    Excel 2002
    Posts
    595

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    See post 6

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    OK, unfamiliar with 2002, but I have a feeling that function came out in later versions of excel

  18. #18
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Withdrawn by FR. Didn't read previous post #13.
    Last edited by FlameRetired; 01-10-2016 at 05:43 PM.

  19. #19
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by drgkt View Post
    ok. That did it! Thanks.

    Can you tell me why the COUNTIF returns 0?
    If you are referring to =COUNTIF((A10:A31);(LEFT((A10:A31);3))="302") it is because there are no lone "302" in A10:A31.

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    FR does does 2002 have COUNTIF()

  21. #21
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by drgkt View Post
    Does not work on office 2002
    What means this? COUNTIF did work in 2002.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  22. #22
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Izandol, does 2003 have countif()? (I cant remember)

  23. #23
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Double posted
    Last edited by FlameRetired; 01-10-2016 at 06:35 PM.

  24. #24
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by FDibbins View Post
    FR does does 2002 have COUNTIF()
    I'm sure it does. My introduction to COUNTIF was 1996-1998. I forget what version of Office we had at that time.
    Last edited by FlameRetired; 01-10-2016 at 06:37 PM.

  25. #25
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    Excel 2002
    Posts
    595

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by FlameRetired View Post
    If you are referring to =COUNTIF((A10:A31);(LEFT((A10:A31);3))="302") it is because there are no lone "302" in A10:A31.
    But we did not specify "lone" "302" we specified the left 3 characters of the entry. Am I wrong?

    Yes COUNTIF exists as a function in 2002.

  26. #26
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    Excel 2002
    Posts
    595

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by Izandol View Post
    What means this? COUNTIF did work in 2002.
    COUNTIF works but not with wildcards in 2002. Read the exact quoted post.

  27. #27
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by drgkt View Post
    But we did not specify "lone" "302" we specified the left 3 characters of the entry. Am I wrong?

    Yes COUNTIF exists as a function in 2002.
    No. You are not wrong about that. What I was trying to draw attention to is that COUNTIF will go searching for the string "302" in a range which does not include a "302" by itself. If you cannot use a wildcard AND you wish to use COUNTIF a rather awkward array formula approach is going to have to be applied to the criteria argument. This should work unless 2002 also has further limitations on the criteria argument. It must be array entered.

    =SUM(IF((COUNTIF(A10:A31,IF(LEFT(A10:A31,3)="302",A10:A31))),1))

    Try applying Evaluate formula to both your original formula and this one. You will be able to see the difference in action.

    And BTW.
    COUNTIF works but not with wildcards in 2002. Read the exact quoted post.
    I am sure we all read the exact quoted post. It was not clear ... at least not to me ... which part was not working. Until now I was never aware of a version of COUNTIF that didn't accept wildcards so the thought never seriously occurred to me. I am aware now so thank you for alerting us to that.
    Last edited by FlameRetired; 01-10-2016 at 07:12 PM.

  28. #28
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    Excel 2002
    Posts
    595

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    If you see A2, I can get the result via SUMPRODUCT.

    I was just beating my head why COUNTIF does not work. I still do not get it. The formula is correct logically and the syntax is correct...

  29. #29
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    I still do not get it. The formula is correct logically and the syntax is correct...
    Yes. That is exactly right. That is part of the problem. In fact believe it or not it is so exactly correct that COUNTIF returns 0.

    Would you like me to guide you through the steps in both versions of the formula so you can see why?

    It will require your participation and use of the F9 function key. If you are not familiar with what can be done with F9 in each of the steps you can think of it as an alternative to the Evaluate Formula feature. I use it all the time.

  30. #30
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    FR, Fx is also a really good tool, although also not sure if 2002 has that either

  31. #31
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by FDibbins View Post
    FR, Fx is also a really good tool, although also not sure if 2002 has that either
    Yes. Ford I started to include that, and then it occurred to me that it might not be. Additionally when I tried to run the original COUNTIF through Fx it stubbornly returned #VALUE error on the LEFT(A10:A31,3) part. Changing the range to A11:A30 did the same. Geeesh!

    Thank you for staying with us on this one. I for one am learning a lot.

  32. #32
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by drgkt View Post
    COUNTIF works but not with wildcards in 2002. Read the exact quoted post.
    Where did you say this before? Only said it did not work. COUNTIF did always support wildcards but wildcard will not work with number values (your 302xxxxx of course is not number): https://support.microsoft.com/en-gb/kb/214153

  33. #33
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    Excel 2002
    Posts
    595

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by Izandol View Post
    Where did you say this before? Only said it did not work. COUNTIF did always support wildcards but wildcard will not work with number values (your 302xxxxx of course is not number): https://support.microsoft.com/en-gb/kb/214153
    Sorry for the misunderstanding.
    In post #6 I stated "=COUNTIF(A10:A31,"302*") does not work in office 2002". I meant the wildcard, not the function.
    I also mentioned that the entries are entered as text. I can now see why.
    Last edited by drgkt; 01-12-2016 at 06:29 AM.

  34. #34
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    Excel 2002
    Posts
    595

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    Quote Originally Posted by FlameRetired View Post
    Yes. That is exactly right. That is part of the problem. In fact believe it or not it is so exactly correct that COUNTIF returns 0.

    Would you like me to guide you through the steps in both versions of the formula so you can see why?

    It will require your participation and use of the F9 function key. If you are not familiar with what can be done with F9 in each of the steps you can think of it as an alternative to the Evaluate Formula feature. I use it all the time.
    Yes, please!
    I am not familiar with the F9, and trying to use evaluate did not sink either!

  35. #35
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    @ drgkt,

    This will take me some time to organize my thoughts and do all the necessary copy / paste. I have some competing tasks to complete as well.

    Glad to see you coming back for this one.

  36. #36
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,876

    Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

    To start if you were not aware of it in arrays the semi-colons are considered row separators. Commas are column separators. There are no column separators in the following arrays to be concerned with.

    All highlighting and use of the F9 key is carried out in the formula bar.

    Working this formula:

    =COUNTIF(A10:A31
    ,LEFT((A10:A31),3)="302")

    First select only the A10:A31 in the first or range argument and press the F9 function key.

    We see this, the range argument, as an array.

    =COUNTIF({"-";"11770";"11880";"11880";"11880";"13830";"13830";"13830";"14584";"14584";"14584";"30210xxxxxxx";"30210xxxxxxx";"210xxxxxxx";"210xxxxxxx";"306xxxxxxxxx";"306xxxxxxxxx";"6xxxxxxxxx";"6xxxxxxxxx";"801xxxxxxxxx";"801xxxxxxxxx";"-"}
    ,LEFT((A10:A31),3)
    ="302")

    Next while still in edit mode select the LEFT((A10:A31),3)
    part and hit F9 again.

    We see:

    =COUNTIF({"-";"11770";"11880";"11880";"11880";"13830";"13830";"13830";"14584";"14584";"14584";"30210xxxxxxx";"30210xxxxxxx";"210xxxxxxx";"210xxxxxxx";"306xxxxxxxxx";"306xxxxxxxxx";"6xxxxxxxxx";"6xxxxxxxxx";"801xxxxxxxxx";"801xxxxxxxxx";"-"}, {"-";"117";"118";"118";"118";"138";"138";"138";"145";"145";"145";"302";"302";"210";"210";"306";"306";"6xx";"6xx";"801";"801";"-"}="302")

    All of the 3 left most characters from A10:A31 are in the second argument or criteria array. But the argument is not complete. We need to also include the =”302” part. Extend the last selection to include that as well and hit the F9 key.

    We see this.

    =COUNTIF({"-";"11770";"11880";"11880";"11880";"13830";"13830";"13830";"14584";"14584";"14584";"30210xxxxxxx";"30210xxxxxxx";"210xxxxxxx";"210xxxxxxx";"306xxxxxxxxx";"306xxxxxxxxx";"6xxxxxxxxx";"6xxxxxxxxx";"801xxxxxxxxx";"801xxxxxxxxx";"-"}, {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})

    At this stage we see that COUNTIF supplied with that output of the criteria argument is told to find the literal strings TRUE and FALSE (and that is how COUNTIF interprets) within the first argument A10:A31 seen in the array

    {"-";"11770";"11880";"11880";"11880";"13830";"13830";"13830";"14584";"14584";"14584";"30210xxxxxxx";"30210xxxxxxx";"210xxxxxxx";"210xxxxxxx";"306xxxxxxxxx";"306xxxxxxxxx";"6xxxxxxxxx";"6xxxxxxxxx";"801xxxxxxxxx";"801xxxxxxxxx";"-"}

    It finds none and returns 0.

    We can usually F9 our way through any formula in this manner. Should Evaluate Formula feature be available that also works and is much faster than the tedious F9 method. The view port is quite small though. There are other differences. Know that sometimes the F9 method can produce misleading results. This is comparatively rare and I won’t elaborate here.

    You’ve chosen SUMPRODUCT. In my opinion that is the wise choice in this case.

    Did this help?
    Last edited by FlameRetired; 01-13-2016 at 02:32 PM.

+ 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. = EMBED("Forms.ComboBox.1","") error "Reference not valid."
    By CatharinaCatharina in forum Excel General
    Replies: 2
    Last Post: 12-11-2014, 09:58 AM
  2. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  3. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  4. [SOLVED] Expanded and collapsed the rows by toggle "+" or "-" sign on left of the text
    By Rocky2013 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-24-2013, 02:49 PM
  5. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  6. Replies: 5
    Last Post: 06-26-2006, 09:23 PM
  7. IF(COUNTIF(Thursday,"John Doe")>1,"error"," ")
    By jayd77 in forum Excel General
    Replies: 3
    Last Post: 10-26-2005, 09:17 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