+ Reply to Thread
Results 1 to 9 of 9

Countif & Sumif with single and double digit numbers mixed in same column.

  1. #1
    Registered User
    Join Date
    12-10-2012
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    20

    Countif & Sumif with single and double digit numbers mixed in same column.

    Have some data I cut and paste from an online database into excel. Trying to use countifs and sumifs. One of the criteria ranges has mixed single and double digit numbers pasted from the online database. My formula works with double digit numbers but not single digit numbers. I had a similar problem with this data in if statement a few months ago and got help from Vlady using the following.

    =IF(ISNUMBER(SEARCH(" "&$A$1&" "," "&SUBSTITUTE(A4,CHAR(160),CHAR(32))&" ")),TRUE,FALSE)

    I can't figure out how to utilize this same theory in Countifs or sumifs.

    My example is attached. Thanks for helping
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Countif & Sumif with single and double digit numbers mixed in same column.

    you've 3 seven times so why it's wrong result?

  3. #3
    Registered User
    Join Date
    12-10-2012
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Countif & Sumif with single and double digit numbers mixed in same column.

    I'm looking for "3" not 23 or 43 is my issue. thanks

    Quote Originally Posted by sandy666 View Post
    you've 3 seven times so why it's wrong result?

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Countif & Sumif with single and double digit numbers mixed in same column.

    so remove first star from E9 and F9 and will be 4 times

  5. #5
    Registered User
    Join Date
    12-10-2012
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Countif & Sumif with single and double digit numbers mixed in same column.

    Yes Sandy but not for the right reason. It is picking up the "3" in 31 in cell A10 because the 3 is the first entry in the cell. So it is a coincidence it worked there. It does not however work correctly in the rest of the table. Sorry but thanks anyway. I'm pretty sure I need to use the char 160 and char 32 again somehow. It works but I can't figure out how to apply it in my two formulas.

    Quote Originally Posted by sandy666 View Post
    so remove first star from E9 and F9 and will be 4 times

  6. #6
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Countif & Sumif with single and double digit numbers mixed in same column.

    e7 cell
    =SUM(IF(ISERROR(SEARCH(" "&D7&" "," "&SUBSTITUTE(TRIM($A$7:$A$21),CHAR(160),"")&" ")),0,1)) Ctrl+shift+enter

    then drag dovvn
    Attached Files Attached Files
    Appreciate the help? CLICK *

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Countif & Sumif with single and double digit numbers mixed in same column.

    AZ-XL: that's a nice solution. Good job. I have modified the second part the (the sumif bit) to take account of possible different values of "amount". That bit in yours wasn't right.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  8. #8
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Countif & Sumif with single and double digit numbers mixed in same column.

    Yes you right. I have noticed it just now. Thank you

  9. #9
    Registered User
    Join Date
    12-10-2012
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Countif & Sumif with single and double digit numbers mixed in same column.

    Glen Kennedy and AZ-XL thank you very much. Your solution is working great! Solved this difficult one well !

+ 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] How to subtract single cells with double digits into a single digit of a single cell.
    By greenfox74 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-11-2021, 02:05 AM
  2. Combining SUMIF and Subtotal for a single column of numbers
    By mosesthetank in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-20-2014, 04:15 PM
  3. [SOLVED] LOTTERY FILTER#4, Find if 1 Digit Sum of 2 Digit or 3 Digit, Single Cell w/ dash
    By david gonzalez in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-08-2014, 12:57 AM
  4. Delete leading zeros from all numbers present in a single, mixed cell
    By daedelous00 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2013, 04:47 PM
  5. [SOLVED] Single cell - double digit subtraction???
    By greenfox74 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-09-2012, 12:30 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