+ Reply to Thread
Results 1 to 100 of 100

Sum values based on range of text AND numbers

  1. #1
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Sum values based on range of text AND numbers

    I tried to sum the values based on the below logic.

    if A1:A16 = YJK001 to YJK100 then sum values
    if A1:A16 = YJK101 to YJK200 then sum values
    if A!:A16 = YJK201 to YJK300 then sum values

    Data
    YJK113 5,023.00
    YJK107 5,034.00
    YJK143 3,927.00
    YJK127 3,594.00
    YJK213 1,439.00
    YJK377 922
    YJK171 1,161.00
    YJK227 4,161.00
    YJK035 6,034.00
    YJK913 4,096.00
    YJK023 5,418.00
    YJK941 4,159.00
    YJK063 5,418.00
    YJK237 1,642.00
    YJK207 1,019.00
    YJK167 1,514.00



    So the result will be :-

    YJK001 to YJK100 = 16,870.00
    YJK101 to YJK200 = 20,253.00
    YJK201 to YJK300 = 8,261.00
    YJK301 to YJK400 = 922.00
    YJK401 to YJK500 = 0.00
    YJK501 to YJK600 = 0.00
    YJK601 to YJK700 = 0.00
    YJK701 to YJK800 = 0.00
    YJK801 to YJK900 = 0.00
    YJK901 to YJK1000 = 8,255.00

    Any Help Please , Thank you.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Try

    =SUMPRODUCT(($B$1:$B$16)*(RIGHT($A$1:$A$16,3)+0>=1)*(RIGHT($A$1:$A$16,3)+0<=100))

    =SUMPRODUCT(($B$1:$B$16)*(RIGHT($A$1:$A$16,3)+0>=101)*(RIGHT($A$1:$A$16,3)+0<=200))

    etc

    OR


    =SUMPRODUCT(($B$1:$B$16)*(RIGHT($A$1:$A$16,LEN($A$1:$A$16)-3)+0>=1)*(RIGHT($A$1:$A$16,LEN($A$1:$A$16)-3)+0<=100))

    if numbers are > 999 e,g YJK1123

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Sum values based on range of text AND numbers

    Try

    =SUMPRODUCT((RIGHT($A$1:$A$16,3)>=MID($A19,4,3))*(RIGHT($A$1:$A$16,3)<=MID($A19,14,3))*($B$1:$B$16))

    Check the attached workbook.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Sum values based on range of text AND numbers

    Hi,

    If those are the only bands, you may use SUMIFS thus

    A
    B
    C
    D
    E
    F
    1
    YJK113
    5,023.00
    YJK001 YJK100
    =SUMIFS(B:B,A:A,">="&D1,A:A,"<="&E1)
    2
    YJK107
    5,034.00
    YJK101 YJK200
    =SUMIFS(B:B,A:A,">="&D2,A:A,"<="&E2)
    3
    YJK143
    3,927.00
    YJK201 YJK300
    =SUMIFS(B:B,A:A,">="&D3,A:A,"<="&E3)
    4
    YJK127
    3,594.00
    YJK301 YJK400
    =SUMIFS(B:B,A:A,">="&D4,A:A,"<="&E4)
    5
    YJK213
    1,439.00
    YJK401 YJK500
    =SUMIFS(B:B,A:A,">="&D5,A:A,"<="&E5)
    6
    YJK377
    922
    YJK501 YJK600
    =SUMIFS(B:B,A:A,">="&D6,A:A,"<="&E6)
    7
    YJK171
    1,161.00
    YJK601 YJK700
    =SUMIFS(B:B,A:A,">="&D7,A:A,"<="&E7)
    8
    YJK227
    4,161.00
    YJK701 YJK800
    =SUMIFS(B:B,A:A,">="&D8,A:A,"<="&E8)
    9
    YJK035
    6,034.00
    YJK801 YJK900
    =SUMIFS(B:B,A:A,">="&D9,A:A,"<="&E9)
    10
    YJK913
    4,096.00
    YJK901 YJK1000
    =SUMIFS(B:B,A:A,">="&D10)
    11
    YJK023
    5,418.00
    12
    YJK941
    4,159.00
    13
    YJK063
    5,418.00
    14
    YJK237
    1,642.00
    15
    YJK207
    1,019.00
    16
    YJK167
    1,514.00


    which will produce your results

    A
    B
    C
    D
    E
    F
    1
    YJK113
    5,023.00
    YJK001 YJK100
    16870
    2
    YJK107
    5,034.00
    YJK101 YJK200
    20253
    3
    YJK143
    3,927.00
    YJK201 YJK300
    8261
    4
    YJK127
    3,594.00
    YJK301 YJK400
    922
    5
    YJK213
    1,439.00
    YJK401 YJK500
    0
    6
    YJK377
    922
    YJK501 YJK600
    0
    7
    YJK171
    1,161.00
    YJK601 YJK700
    0
    8
    YJK227
    4,161.00
    YJK701 YJK800
    0
    9
    YJK035
    6,034.00
    YJK801 YJK900
    0
    10
    YJK913
    4,096.00
    YJK901 YJK1000
    8255
    11
    YJK023
    5,418.00
    12
    YJK941
    4,159.00
    13
    YJK063
    5,418.00
    14
    YJK237
    1,642.00
    15
    YJK207
    1,019.00
    16
    YJK167
    1,514.00
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    @Lovemyexcel: spoilt for choice! but SUMIFS is best solution. ( i like SUMPRODUCT because of its versatility!)

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Sum values based on range of text AND numbers

    I did neglect one (possibly) important limitation of my version in that it will not pick up YJK1000 correctly if there actually is such an item.

  7. #7
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Thanks, will try it again , I am not getting the correct results

  8. #8
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by shukla.ankur281190 View Post
    Try

    =SUMPRODUCT((RIGHT($A$1:$A$16,3)>=MID($A19,4,3))*(RIGHT($A$1:$A$16,3)<=MID($A19,14,3))*($B$1:$B$16))

    Check the attached workbook.
    Thanks , I do agree with the others that =SUMPRDUCT is desirable for such logical code.

    I am sorry I am not getting correct results , My be I did not explain that data in Column A can go up to 4 digit , I am attaching your sample sheet and colored it RED for the wrong results.

    ( Green color represents the correct result )


    Correct results Code calculated results
    YJK001 to YJK100 20373 29373
    YJK101 to YJK200 17253 20253
    YJK201 to YJK300 8261 15263
    YJK301 to YJK400 922 922
    YJK401 to YJK500 0 0
    YJK501 to YJK600 0 0
    YJK601 to YJK700 0 3501
    YJK701 to YJK800 0 0
    YJK801 to YJK900 6000 6000
    YJK901 to YJK1000 13255 0
    YJK1001 to YJK1100 9000 59194
    YJK1101 to YJK1200 0 54160
    YJK1201 to YJK1300 7002 49137
    Last edited by Lovemyexcel; 02-20-2017 at 11:01 AM.

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Sum values based on range of text AND numbers

    I do not quite agree with all your totals. Is this version correct?

  10. #10
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Thanks, it is correct.

    My actual data extends from YJK001 up to YJK9999, with different intervals, can I continue the formula.?

    MY YJK Range is:-

    0001-0019
    0020-0899
    0900-0999
    1000-1999
    2000-2029
    2030-2049
    2050-2899
    2900-2999
    3000-3019
    3020-3049
    3050-3899
    3900-3999
    4000-4999
    5000-5999
    6000-6249
    6250-6699
    6700-6799
    6800-6899
    6900-6999
    7000-7999
    8000-8999
    9000-9009
    9010-9039
    9040-9049
    9050-9059
    9060-9069
    9070-9089
    9090-9099
    9100-9199
    9200-9299
    9300-9599
    9600-9799
    9800-9899
    9900-9949
    9950-9999

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Sum values based on range of text AND numbers

    The formula should cater for that. You need only have a start value in one column and the stop value in the next. The interval is not important to the formula.

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Sum values based on range of text AND numbers

    Try with SUMPRODUCT as in attachment.
    Quang PT

  13. #13
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by xlnitwit View Post
    The formula should cater for that. You need only have a start value in one column and the stop value in the next. The interval is not important to the formula.
    Thanks, I will try it in real data and let you know.

  14. #14
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by bebo021999 View Post
    Try with SUMPRODUCT as in attachment.
    Thank you , Let me try it also.

    You all are very kind and helpful.

  15. #15
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by xlnitwit View Post
    The formula should cater for that. You need only have a start value in one column and the stop value in the next. The interval is not important to the formula.
    The formula work perfect, but because the data in row A and B are not constant , some times it is 2570 records , 240 , 3502 etc. there is no fixed record , the formula gives ERROR.

    In the attaché workbook, the previous record in ROW A was 2585, but when the new record came as 2568 the formula gave error.

    Is there any way we can add a string in the formula to only consider the available record and disregard BLANK records.

  16. #16
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Can someone help please.

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Added named ranges:

    YJK

    Refers to: =OFFSET(Sheet1!$A$1,,,COUNTIF(Sheet1!$A:$A,"?*"),1)

    Amount


    refers to: =OFFSET(Sheet1!$B$1,,,COUNT(Sheet1!$B:$B),1)

    Formula

    =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT(F2,4))*Amount)-SUM($G$1:$G1)
    Last edited by JohnTopley; 03-07-2017 at 06:48 AM.

  18. #18
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Thank you John.


    The Name Range is excellent idea and works, this triggered me to add a column for "Label names" too, so that exact amount is placed in the correct number ranges. I know it is a challenge but appreciate any help in achieving it.

    I will post the sample sheet with explanations as soon as I finish the data entry.

  19. #19
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by JohnTopley View Post
    Added named ranges:

    YJK

    Refers to: =OFFSET(Sheet1!$A$1,,,COUNTIF(Sheet1!$A:$A,"?*"),1)

    Amount


    refers to: =OFFSET(Sheet1!$B$1,,,COUNT(Sheet1!$B:$B),1)

    Formula

    =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT(F2,4))*Amount)-SUM($G$1:$G1)
    Hello ,

    Here is a challenge :-

    I have added Column B for ID number, where by IF the Number range matches any of the Column B ID then it adds the value.


    The current formula only considers Column A and C

    Column I2 =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT(H2,4))*Amount)-SUM($I$1:$I1)


    I want the formula also considers Column B to distribute the cost according to Number.

    The IDs are listed in Column K.
    See Attached sample sheet.

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    You will need VBA to do this as the format of the codes is very variable: sometimes the full text e.g. YZAI4, other times only partial text e.g. YZAK.

    Even with "wildcards" it is likely to a be complex formula.

    PLUS all the codes appear to have trailing blanks.
    Last edited by JohnTopley; 03-08-2017 at 11:52 AM.

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Can you please supply result for range 0020-0899 for selected codes.

    with this formula ...

    =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT(H3,4))*(ISNUMBER(SEARCH({"YZAK","YZAS","YZAE","YZAR","YZAQ","OKOE","OKPE","TCOC","TFAA"},Codes))*Amount)-SUM($I$1:$I2))

    I get result of $11,837.20 for 0020-0899

    Is this correct?

    If so, you can adjust the above with the correct codes for the other ranges.

    I believe the above formula now needs amending as we can no longer take away the SUM portion.

    Try

    for "0020-0899"

    =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)>=--LEFT(H3,4))*(--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT(H3,4))*(ISNUMBER(SEARCH({"YZAK","YZAS","YZAE","YZAR","YZAQ","OKOE","OKPE","TCOC","TFAA"},Codes)))*Amount)
    Last edited by JohnTopley; 03-08-2017 at 01:28 PM.

  22. #22
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by JohnTopley View Post
    Can you please supply result for range 0020-0899 for selected codes.

    with this formula ...

    =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT(H3,4))*(ISNUMBER(SEARCH({"YZAK","YZAS","YZAE","YZAR","YZAQ","OKOE","OKPE","TCOC","TFAA"},Codes))*Amount)-SUM($I$1:$I2))

    I get result of $11,837.20 for 0020-0899

    Is this correct?

    If so, you can adjust the above with the correct codes for the other ranges.

    I believe the above formula now needs amending as we can no longer take away the SUM portion.

    Try

    for "0020-0899"

    =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)>=--LEFT(H3,4))*(--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT(H3,4))*(ISNUMBER(SEARCH({"YZAK","YZAS","YZAE","YZAR","YZAQ","OKOE","OKPE","TCOC","TFAA"},Codes)))*Amount)
    Thanks John,
    This is correct, I will apply it to the rest of the formula with real data and post the results.

  23. #23
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by Lovemyexcel View Post
    Thanks John,
    This is correct, I will apply it to the rest of the formula with real data and post the results.
    I entered the formula in I3 but got 0 result.

    Can you please post a sample sheet with the formula in I3.

    Thank you.

  24. #24
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    See attached: cells in GREEN have new formulae.

    In Column D I "extracted" the numbers from Column A and the bulk are < 1000 i.e. in the 0020-0899 group so values of 0 are correct (?) for many of the other groups.

  25. #25
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by JohnTopley View Post
    See attached: cells in GREEN have new formulae.

    In Column D I "extracted" the numbers from Column A and the bulk are < 1000 i.e. in the 0020-0899 group so values of 0 are correct (?) for many of the other groups.
    Thank you John,
    The formula looks Ok , but notice that the number range 0001-0019 is not picking up the values in C15 and C16 , I must have done something wrong.

    0001-0019 $0.00

    YJK001 YZHM1 $2,436.65
    YJK002 YZMF2 $1,486.61

  26. #26
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Wrong (old) formula in cell (my error!):

    in J2

    =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)>=--LEFT(I3,4))*(--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT(I3,4))*(ISNUMBER(SEARCH({"YZAK","YZAS","YZAE","YZAR","YZAQ","OKOE","OKPE","TCOC","TFAA"},Codes)))*Amount)

  27. #27
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Yes , it works, Thank you.

    My data range is variable it might contain more than 18000 records, will there be a problem?

  28. #28
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    It might be slow with that volume of records and the addition of the CODE chack: I can only say try it and see!

  29. #29
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by JohnTopley View Post
    It might be slow with that volume of records and the addition of the CODE chack: I can only say try it and see!
    Thank you, Appreciated,

    I am checking with real data , though I am facing #N/A error , but still trying to resolve it.

    I will let you know.

  30. #30
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    I am actually coping your formula to another worksheet , still trying to resolve the #N/A error.

  31. #31
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello,
    I am getting favorable results with actual data so far, but got a slight problem in the Named data for "Codes" in Column B.

    The problem is that the Column B length need to be exactly as the data availability, my data is some times less and some times more.

    Is there any way we can also count Column B for "Codes" so that the formula only considers the available values in column B.

    Thank you.

  32. #32
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Change the COUNT in named range OFFSET to reference column B for all the named ranges.

  33. #33
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Thanks,

    I will change the "Codes" to =OFFSET(Sheet1!$B$1,,,COUNT(Sheet1!$B:$B),1)

    and test it again.
    Last edited by Lovemyexcel; 03-11-2017 at 12:51 PM.

  34. #34
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello John

    Thank you very much, I tried it on real data and even added more calculation columns, so far the results are excellent.

    Appreciated.

  35. #35
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Thank you very much for the feedback: hope the good news continues!

  36. #36
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello John,
    Thank you, I used over 18000 records and works fine, just a slight issue that just came up with the new data I received.

    It turned up that the Name Range YJK in Column A comes with prefixes some times.

    e.g.

    YJK123A or YJK1111B or C etc.

    this is causing and error in the formula,

    Is there a way for the Name Range to also accept the prefixes ?

  37. #37
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    I assume we need to remove the suffix .... so add new column with the following formula

    =IF(ISNUMBER(RIGHT(A1,1)),A1,LEFT(A1,LEN(A1)-1))

    Then change the named range YJK to reference the new column

  38. #38
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello Joahn,

    Thanks, excellent idea, I tried with a test data and it works, let me try it tomorrow on real data .

    Will let you know.

  39. #39
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by JohnTopley View Post
    I assume we need to remove the suffix .... so add new column with the following formula

    =IF(ISNUMBER(RIGHT(A1,1)),A1,LEFT(A1,LEN(A1)-1))

    Then change the named range YJK to reference the new column
    Hello,

    The above formula works ok when the number have suffix , but Unfortunately numbers with out suffix are also getting striped off one number.

    e.g. for YJK123D the formula is showing YJK123
    for YJK123 the formula is showing YJK12

    I don't know if we can adjust the formula to only strip the suffixes .

    Thanks.
    Last edited by Lovemyexcel; 03-15-2017 at 11:15 PM.

  40. #40
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Try

    =IF(ISNUMBER(RIGHT(A1,1)+0),A1,LEFT(A1,LEN(A1)-1))

  41. #41
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Sum values based on range of text AND numbers

    For what it's worth here is another approach.

    This makes heavy use of Name Manager to parse and organize the 'CONDITIONS REQUIRED' strings.

    StripCondReq
    This standardizes the strings in the conditions cells.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Words
    This returns a count of the individual conditions in each cell.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ArrayCondReq
    This parses the conditions and puts them into an array of conditions.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then in column D this formula unfortunately has to be array entered, but it strips out all non-numbers including suffixes. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then the final formula in column L returns the same values as in your upload. Additionally the 0001-0019 range and others get returns.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I don't know how well any of this is going to do with that many rows.
    Dave

  42. #42
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Thank you John. I will keep testing it, it is displaying the numbers now.

    Thank you Flame,
    Another approach , easy , worth a try , I will test it on extended data.


    Just realized with a new load of data , some rows in column B come with blank value and thus the calculation will not match or it will give an error.

    How can we identify those blank rows thru a formula and correct them.
    Last edited by Lovemyexcel; 03-19-2017 at 06:56 AM.

  43. #43
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Sum values based on range of text AND numbers

    If I understand correctly my approach handles that. For example: If I delete YZMF2 in cell B16 the total in L2 returns a total that does not include YZMF2.

    What am I missing?

  44. #44
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Are you saying we need totals where the code is a blank?

    If so just add ," " to the code list

    =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)>=--LEFT(I2,4))*(--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT(I2,4))*(ISNUMBER(SEARCH({"YZHM","YZMF","YZRC","YZ124","YZAS99","YZAKF","RSF"," "},Codes)))*Amount)
    Last edited by JohnTopley; 03-20-2017 at 05:10 AM.

  45. #45
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by FlameRetired View Post
    If I understand correctly my approach handles that. For example: If I delete YZMF2 in cell B16 the total in L2 returns a total that does not include YZMF2.

    What am I missing?

    Hello Flame,

    Actually , If you leave any row blank in column D , or entered a code other than the Code Names defined, the total will change. I just checked.

  46. #46
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Thanks John,

    This will also could be a case , Will try it.

  47. #47
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello Flame,

    The formula is working excellent with some results I still need to verify with as many as 20000 records data.

    But to let you know , Actually , If you leave any row blank in column B , or entered a code other than the Code Names defined, the total will change. I just checked.

  48. #48
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by Lovemyexcel View Post
    Hello Flame,

    Actually , If you leave any row blank in column D , or entered a code other than the Code Names defined, the total will change. I just checked.
    The formula is working excellent with some results I still need to verify with as many as 20000 records data.

  49. #49
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Sorry Flame,

    Actually , If you leave any row blank in column D , or entered a code other than the Code Names defined, the total will change. I just checked.

    The formula is working excellent with some results I still need to verify with as many as 20000 records data.

  50. #50
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello Flame,

    Again Sorry,
    The website is not allowing me Reply with Quote:

    I am sending it again.

    Actually , If you leave any row blank in column B , or entered a code other than the Code Names defined, the total will change. I just checked.

    The formula is working excellent with some results I still need to verify with as many as 20000 records data.
    Last edited by Lovemyexcel; 03-20-2017 at 02:03 PM.

  51. #51
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    [QUOTE=JohnTopley;4609336]Are you saying we need totals where the code is a blank?

    If so just add ," " to the code list


    Hello John,

    I tried the blank," " but it still giving error if rows in column B are left blank.
    Last edited by Lovemyexcel; 03-22-2017 at 11:47 AM.

  52. #52
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    I added a "blank" to the code list and removed the code form column B and still got the correct sum.

    Without the "blank" in the code list, but removing the code from column B, I produced a different (reduced) total; this the same result as having a code not in the code list used in the SUMPRODUCT e.g code "ABCDE" not in column B but in the SUMPRODUCT list.

  53. #53
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by JohnTopley View Post
    I added a "blank" to the code list and removed the code form column B and still got the correct sum.

    Without the "blank" in the code list, but removing the code from column B, I produced a different (reduced) total; this the same result as having a code not in the code list used in the SUMPRODUCT e.g code "ABCDE" not in column B but in the SUMPRODUCT list.
    Thank You John
    Can you kindly post the sample sheet with your code.

  54. #54
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Try this



    =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)>=--LEFT($I2,4))*((--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT($I2,4)))*Amount)+SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)>=--LEFT($I3,4))*(--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT($I3,4))*(ISNUMBER(SEARCH({"YZHM","YZMF","YZRC","YZ124","YZAS99","YZAKF","RSF"},Codes)))*Amount)

    Highlighted SUMs without checking for CODE values: if it works, just add to the existing formulae.

    See Sheet2: highlighted entries for J2
    Last edited by JohnTopley; 03-23-2017 at 04:33 AM.

  55. #55
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Thanks, will try it.

  56. #56
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello John,
    At this point , I can confirm that the application is working fine.

    I have another issue related to compairing Track numbers with vendors , Do you want me to post it here or create a new topic.?
    I really appreciate your help.
    Thank you.

  57. #57
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello Flame,
    I also used your formula in parallel for other sorting with a little modification which is working fine. Thank you very much.

    But still the issue of the blank raw in Column B not resolved.

    I would really appreciate if this can be solved.

  58. #58
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Start a new post please and ....

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  59. #59
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by JohnTopley View Post
    Start a new post please and ....

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Thanks John , I will , but just last fix if possible please:

    Is it possible to identify what numbers and codes in the column A and B are used to get the total sum.

    e.g.

    0001-0019 $3,923.26 "YZHM" , "YZMF" , "YZRC" , "YZ124" , "YZAS99" , "YZAKF", "RSF"


    What are the numbers and codes used in column A and B to total $ 3,923.26

    Appreciate your help.

  60. #60
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    I found an error in my formula:

    =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)>=--LEFT($I2,4))*((--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT($I2,4)))*Amount)+SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)>=--LEFT($I2,4))*(--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT($I2,4))*(ISNUMBER(SEARCH({"YZHM","YZMF","YZRC","YZ124","YZAS99","YZAKF","RSF"},Codes)))*Amount)

    so it will double count.

    so it is back to the drawing board for counting "blanks" and I don't know if you can identify which codes applied to an total without a completely different approach (probably involving helper column(s).

    This will work if there is a space rather then "null" (empty cell):

    =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)>=--LEFT($I2,4))*(--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT($I2,4))*(ISNUMBER(SEARCH({"YZHM","YZMF","YZRC","YZ124","YZAS99","YZAKF","RSF"," "},Codes)))*Amount)

    but not the cell is "Null" e.g "Clear Contents"

  61. #61
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    ... and many codes are common to many ranges so not sure how to approach this!

    Can you replace the blanks with a "special code" : you could use "Find and Replace" to add the "Special code" e.g "xxx"

    The add "xxx" into code lists.

    =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)>=--LEFT($I2,4))*(--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT($I2,4))*(ISNUMBER(SEARCH({"YZHM","YZMF","YZRC","YZ124","YZAS99","YZAKF","RSF","xxx"},Codes)))*Amount)

    See the attached with an approach to SUM for each individual code:

    In O2

    =IF(ISNUMBER(SEARCH(O$1,$M2)),SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)>=--LEFT($I2,4))*(--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT($I2,4))*(ISNUMBER(SEARCH(O$1,Codes)))*Amount),"")

    Copy across and down

    in N2

    =SUM(O2:AO2)

    If you choose the "xxx" solution for blanks" then Add "xxxx" into row 1
    Last edited by JohnTopley; 03-26-2017 at 12:02 PM.

  62. #62
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Sum values based on range of text AND numbers

    From post #57
    Quote Originally Posted by Lovemyexcel View Post
    Hello Flame,
    I also used your formula in parallel for other sorting with a little modification which is working fine. Thank you very much.

    But still the issue of the blank raw in Column B not resolved.

    I would really appreciate if this can be solved.
    I do not understand what you mean.

    Perhaps an example would help:
    At the moment the sum in cell L2 is $3,923.26. If I delete (make blank) one of the Codes within the range 0001-0019 ... for example B16 ... the sum in L2 is now $2,436.65. It is my understanding that would be correct.

    What am I missing?

  63. #63
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Thank you Flame,
    This is correct, in fact I also added a trigger last night and got your formula work perfect, Thank you.
    One more issue challenging before closing this thread is, extracting in separate sheet, the track numbers in Column A and B for the values in column L.

    E.g. the track numbers in Column A and B that added up the sum of $3,923.26 in Column L2 for number range 0001-0019 are:-

    YJK001 YZHM1
    YJK002 YZMF2

    Thank you for your continues support.
    Last edited by Lovemyexcel; 03-27-2017 at 12:42 AM.

  64. #64
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello Flame,

    I am attaching a sample workbook.

    In sheet 2 you will see example of what I mentioned regarding extracting track numbers.

  65. #65
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello Flame,

    I believe there was a problem uploading the 1st file.

    here is another sample upload file again.
    Attached Files Attached Files

  66. #66
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Removed by JT
    Last edited by JohnTopley; 03-27-2017 at 11:30 AM.

  67. #67
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    In Sheet2

    A2

    =IFERROR(INDEX(YJK,SMALL(IF((Sheet1!$D$1:$D$10000>=--LEFT($A$1,4))*(Sheet1!$D$1:$D$10000<=--RIGHT($A$1,4)),ROW(Sheet1!$A$1:$A$10000),""),ROWS($1:1))),"")

    Enter with Ctrl+Shift+Enter

    Copy down

    in B2

    =IFERROR(VLOOKUP(A2,Sheet1!$A$1:$B$10000,2,0),"")

    Copy down

    in C2

    =SUMPRODUCT((--RIGHT(YJK,LEN(YJK)-3)>=--LEFT(A$1,4))*(--RIGHT(YJK,LEN(YJK)-3)<=--RIGHT(A$1,4))*(ISNUMBER(SEARCH($B2,Codes)))*Amount)


    Using my named ranges from previous posts

    You should create named ranges for columns A and B in Sheet1
    Attached Files Attached Files

  68. #68
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Sum values based on range of text AND numbers

    @ JohnTopley

    FWIW I am experiencing slow upload and slow workbook with both your latest upload and the Lovemyexcel's upload from post #65.

    I believe the "culprit" is my named formula ArrayCondReq. I didn't see this one coming.

    I removed the volatile INDIRECT and changed its definition to

    =TRIM(MID(REPT(" ",LEN(StripCondReq))&SUBSTITUTE(StripCondReq,",",REPT(" ",LEN(StripCondReq))),ROW(INDEX(A:A,1):INDEX(A:A,Words))*LEN(StripCondReq)+1,LEN(StripCondReq)))

    The file may be slow upon 'Workbook open'. It is not slow after that.

    Are you noticing the same?

  69. #69
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by FlameRetired View Post
    @ JohnTopley

    FWIW I am experiencing slow upload and slow workbook with both your latest upload and the Lovemyexcel's upload from post #65.

    I believe the "culprit" is my named formula ArrayCondReq. I didn't see this one coming.

    I removed the volatile INDIRECT and changed its definition to

    =TRIM(MID(REPT(" ",LEN(StripCondReq))&SUBSTITUTE(StripCondReq,",",REPT(" ",LEN(StripCondReq))),ROW(INDEX(A:A,1):INDEX(A:A,Words))*LEN(StripCondReq)+1,LEN(StripCondReq)))

    The file may be slow upon 'Workbook open'. It is not slow after that.

    Are you noticing the same?
    Thanks Flame,
    In the workbook attached to post #65 , where does the above formula go to.

    Can you please upload sample workbook.

    Thank you.

  70. #70
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Sum values based on range of text AND numbers

    It goes in Name Manager.

    Click Formulas > Name Manager. Select 'ArrayCondReq'. Tab down to 'Refers to:'. Paste the new formula into that selection. Hit Enter > Close. The formulas will all refer to the new definition automatically.

    It appears that JohnTopley has solved the last part of your question.

  71. #71
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    See attached: I changed the formula in column D to remove the array formula (very slow):

    =SUBSTITUTE($A1,"YJK","")+0
    Attached Files Attached Files

  72. #72
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Gentlemen, Thank you ,

    Let me peace together the 2 and test it, i will let you know

    Really appreciated.

  73. #73
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Added a new version using SUMIFS

    Rows 1 and 2

    =LEFT(A$3,4)+0 and =RIGHT(A$3,4)+0

    in C4

    =SUMIFS(Sheet1!$C:$C,Sheet1!$B:$B,"=" &B4,Sheet1!$D:$D,">=" &A$1,Sheet1!$D:$D,"<=" &A$2)
    Attached Files Attached Files

  74. #74
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Thank you John, I will try it on your work book.

    Hello Flame,

    Yes, I am still working on John’s formula that uses different approach ( not using the name codes in column M ).

    Your approach is different, in that, it uses the names listed in column M and can be edited easily.

    The workbook is in post #65

    Can you please see to it how you can simplify the data distribution as in sheet 2 using the following example :-

    E.g. the track numbers in Column A and B that added up the sum of $3,923.26 in Column L2 for number range 0001-0019 are:-

    YJK001 YZHM1 $2,436.65
    YJK002 YZMF2 $1,486.61

    Thank you.
    Last edited by Lovemyexcel; 03-28-2017 at 08:48 AM.

  75. #75
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello John,

    What we are trying to achieve is , to turn each value for each track no in Column A and the corresponding match in Column B and C , where the formula totals if Column A and B matches the data in Sheet 1.

    It could happen that a particular track number in Column A operated multiple times a month with different codes in Column B and different rate in column C

    If we can only list the track number in Column A and the totals if Track numbers in Column A and B match , so forth so on


    e.g. the data in Sheet 1 is :-


    YJK001 YZHM1 $2,436.65
    YJK001 YZMF2 $500.00
    YJK001 YZHM1 $200.00
    YJK001 YZMF3 $100.00


    If we can make it to show :-

    YJK001 YZHM1 $2,636.65 ("this is the sum of $2,436.65 in the first line and $200.00 in the 3rd line ( though same track numbers (YJK001), and same codes (YZHM1), but different prices ( $2,436.65 , $200.00 ).

    YJK001 YZMF2 $500.00
    YJK001 YZMF3 $100.00



    But the formula is turning all codes the same and one value :-

    1
    19
    0001-0019 CODE AMOUNT
    YJK001 YZHM1 2636.65
    YJK001 YZHM1 2636.65
    YJK001 YZHM1 2636.65
    YJK001 YZHM1 2636.65

    Thank you.
    Last edited by Lovemyexcel; 03-28-2017 at 09:32 AM.

  76. #76
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    The requirements change yet again:

    You now need a unique list of column A (track number) for each each range and then for each track number the list of codes (and the codes are not unique to a given track number).

    I suspect both A and B will change as you add/delete data. (?)

    I am sure it can be done via formulae but (at this stage) not sure how: I'll leave it to "Flame Retired" unless I have a "eureka" moment!!!

  77. #77
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by JohnTopley View Post
    The requirements change yet again:

    You now need a unique list of column A (track number) for each each range and then for each track number the list of codes (and the codes are not unique to a given track number).

    I suspect both A and B will change as you add/delete data. (?)

    I am sure it can be done via formulae but (at this stage) not sure how: I'll leave it to "Flame Retired" unless I have a "eureka" moment!!!
    Sorry John, I might have not explained it better, but thank you for all the help you provided.

    I hope "Flam Retired" will come up with a solution.

    Thank you.

  78. #78
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Try this:

    Added helper column D:

    =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,SUBSTITUTE($A2,"YJK","")+0,0)



    Any duplicates of A and B are valued as 0

    In Sheet2

    A4

    =IFERROR(TRIM(INDEX(Sheet1!$A$2:$A$10001,SMALL(IF((Sheet1!$D$2:$D$10001>=$A$1)*(Sheet1!$D$2:$D$10001<=$A$2),ROW(Sheet1!$A$2:$A$10001)-ROW($A$2)+1,""),ROWS($A$2:A2)))),"")

    B4

    =IFERROR(TRIM(INDEX(Sheet1!$B$2:$B$10001,SMALL(IF((Sheet1!$D$2:$D$10001>=$A$1)*(Sheet1!$D$2:$D$10001<=$A$2),ROW(Sheet1!$A$2:$A$10001)-ROW($A$2)+1,""),ROWS($A$2:B2)))),"")

    Enter above with Ctrl+shift+Enter

    C4

    =SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,"=" &A4,Sheet1!$B:$B,"=" &B4)

    The above assumes columns A and B are unique i.e. do not repeat in any other range.

    If this is NOT true, then I'll use another helper column E:

    =SUBSTITUTE($A2,"YJK","")+0,0)

    and change C4

    =SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,"=" &A4,,Sheet1!$B:$B,"=" &B4,Sheet1!$E:$E,">=" &$A$1,Sheet1!$E:$E,"<=" &$A$2)
    Attached Files Attached Files
    Last edited by JohnTopley; 03-28-2017 at 12:48 PM.

  79. #79
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    I corrected last SUMIFS formula in the previous post.

  80. #80
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Sum values based on range of text AND numbers

    I am still trying to catch up.

    @Lovemyexcel,

    I have been operating with the understanding that the track numbers could have one or more letters appended to the end.

    I have searched this thread today and can not find where I got that idea.

    Perhaps I dreamt it.

    Is it possible there could be track numbers (column A) such as YJK1808B?

    Edit No. I didn't dream it. Back in posts #37 and 39 there was discussion of it. No examples were in the uploads. Where are we with that part?
    Last edited by FlameRetired; 03-28-2017 at 08:22 PM.

  81. #81
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Thanks to Dave for reminder about suffix to column A.

    Added extra column D to remove suffix (as per post #39):

    =IF(ISNUMBER(RIGHT(A2,1)+0),A2,LEFT(A2,LEN(A2)-1))

    Column E now ...

    =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,SUBSTITUTE($D2,"YJK","")+0,0)

    I assume suffix is unique entry (See Sheet2)
    Attached Files Attached Files

  82. #82
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Thank you John,

    Hello Dave

    Yes , That part related to striping the letters is done on your workbook and working perfect Thank you.

    The only thing left is how to distribute the track numbers, codes and prices as per their respective number range in a separate Sheet.

  83. #83
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    The only thing left is how to distribute the track numbers, codes and prices as per their respective number range in a separate Sheet.
    Hopefully the latest file does that !!!

  84. #84
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello John,

    I tried but got confused now.

    the latest file Test 4 does not have the formulas you mentioned in post #78 and #81 , further more , on both posts you mentioned to add them in the same column D , more confused.

    I assumed you meant Column D and E , but when I entered them I am getting error on both.

    Can you please post a sample sheet.

  85. #85
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello Dave

    I am attaching a clean copy for distribution.

    In sheet 2 is the condition required.

    Thank you.
    Attached Files Attached Files

  86. #86
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    If this is not possible , then just list all the track numbers, codes and amounts in the number range .

    Either way will work.

    Either Sum the totals if track no and CODE matches,

    0020-0899 CODE AMOUNT

    YJK365 YZAQB 647.23
    YJK365 TCOCE 110.7

    OR

    List all track no , CODE and Amounts as is from the data base.

    0020-0899 CODE AMOUNT

    YJK365 YZAQB 647.23
    YJK365 TCOCE 110.7
    YJK365 YZAQB 142.28

    Thank you.
    Last edited by Lovemyexcel; 03-29-2017 at 02:55 AM.

  87. #87
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Sorry wrong file posted ...
    Attached Files Attached Files

  88. #88
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello Dave,

    I would appreciate if you can come up with different approach to the distribution.

    Thank you.

  89. #89
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Hello Dave,

    Either way will work.

    Either Sum the totals if track numbers and CODEs match,

    0020-0899 CODE AMOUNT

    YJK365 YZAQB 647.23
    YJK365 TCOCE 110.7

    OR , List all track no , CODE and Amounts as is from the data base.

    0020-0899 CODE AMOUNT

    YJK365 YZAQB 647.23
    YJK365 TCOCE 110.7
    YJK365 YZAQB 142.28

    Thank you.

  90. #90
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Sum values based on range of text AND numbers

    I have examined JohnTopley's latest upload post #87 is some detail.
    • It appears to do exactly what you ask.
    • The former calculation bottle-necks are no longer there.
    • I see no way to output the distributions without array formulas such as he built.
    • I see no way to improve on this.
    What is it not doing that you want done?

  91. #91
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    It is just that I am seeing slow response when data is big more than 30,000 records.

    I am filtering it and see if i can squeeze it further.

    Thank you.

  92. #92
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by Lovemyexcel View Post
    It is just that I am seeing slow response when data is big more than 30,000 records.
    With that many rows I am not surprised.

  93. #93
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by FlameRetired View Post
    With that many rows I am not surprised.
    This is why I was seeking your help simplifying it if possible.

  94. #94
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    I have just seen Dave's post #90 where he has kindly commented on the solution I offered. I was very conscious of the performance issues and did all I could to mitigate them by removing array formulae where possible.

    I will re-look at the options but at the moment (like Dave) I do not see any "magic" solution.

    You may get some performance improvement by sorting the data by the numeric part of the column A data i.e YJKnnnn (sort on the extracted nnnn).

  95. #95
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by JohnTopley View Post
    I have just seen Dave's post #90 where he has kindly commented on the solution I offered. I was very conscious of the performance issues and did all I could to mitigate them by removing array formulae where possible.

    I will re-look at the options but at the moment (like Dave) I do not see any "magic" solution.

    You may get some performance improvement by sorting the data by the numeric part of the column A data i.e YJKnnnn (sort on the extracted nnnn).
    Thank you ,

    I will try this one also and let you know .

    Appreciate all the help you provided .

    Best Regards.

  96. #96
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    Another version using multiple "helper" columns:

    Column E

    =SUBSTITUTE($D2,"YJK","")+0

    Column G

    =IFERROR(VLOOKUP(E2,$H$2:$I$36,2,1),"")

    column H

    =LEFT(J2,4)+0

    Column I

    numbers 1 to N

    Columns H:I are a "lookup" table used by formula in column G to assign data to its appropriate range

    Columns A:E are SORTED by column E so all ranges are sequential

    in Sheet2

    A4

    =IFERROR(IF(ROWS($1:1)<=COUNTIF(Sheet1!$G:$G,$A$1),INDEX(Sheet1!$A$2:$A$100,MATCH($A$1,Sheet1!$G$2:$G$100,0)+ROWS($1:1)-1),""),"")

    B4

    =IFERROR(IF(ROWS($1:1)<=COUNTIF(Sheet1!$G:$G,$A$1),INDEX(Sheet1!$B$2:$B$100,MATCH($A$1,Sheet1!$G$2:$G$100,0)+ROWS($1:1)-1),""),"")

    C4

    =IFERROR(IF(ROWS($1:1)<=COUNTIF(Sheet1!$G:$G,$A$1),INDEX(Sheet1!$C$2:$C$100,MATCH($A$1,Sheet1!$G$2:$G$100,0)+ROWS($1:1)-1),""),"")

    Higlighted refrence needs to changed for each range (see D4 entry)

    OR change above to use values in Row 2 (=INT((COLUMNS($A:A)-1)/3)+1)

    RESULT: no array formulae used.

    but

    ..you will ...List all track no , CODE and Amounts as is from the data base.
    Attached Files Attached Files
    Last edited by JohnTopley; 03-30-2017 at 05:33 AM. Reason: Added note

  97. #97
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    Quote Originally Posted by JohnTopley View Post
    Another version using multiple "helper" columns:

    Column E

    =SUBSTITUTE($D2,"YJK","")+0

    Column G

    =IFERROR(VLOOKUP(E2,$H$2:$I$36,2,1),"")

    column H

    =LEFT(J2,4)+0

    Column I

    numbers 1 to N

    Columns H:I are a "lookup" table used by formula in column G to assign data to its appropriate range

    Columns A:E are SORTED by column E so all ranges are sequential

    in Sheet2

    A4

    =IFERROR(IF(ROWS($1:1)<=COUNTIF(Sheet1!$G:$G,$A$1),INDEX(Sheet1!$A$2:$A$100,MATCH($A$1,Sheet1!$G$2:$G$100,0)+ROWS($1:1)-1),""),"")

    B4

    =IFERROR(IF(ROWS($1:1)<=COUNTIF(Sheet1!$G:$G,$A$1),INDEX(Sheet1!$B$2:$B$100,MATCH($A$1,Sheet1!$G$2:$G$100,0)+ROWS($1:1)-1),""),"")

    C4

    =IFERROR(IF(ROWS($1:1)<=COUNTIF(Sheet1!$G:$G,$A$1),INDEX(Sheet1!$C$2:$C$100,MATCH($A$1,Sheet1!$G$2:$G$100,0)+ROWS($1:1)-1),""),"")

    Higlighted refrence needs to changed for each range (see D4 entry)

    OR change above to use values in Row 2 (=INT((COLUMNS($A:A)-1)/3)+1)

    RESULT: no array formulae used.

    but
    John, Thank you for your continues support , appreciated.

    When I only changed the data is sheet1 raw A10 to YJK001, the results in sheet 2 is wrong.

    1) I don't know how track no YJK020 got placed in the number range 0001-0019 though it is already in the 0020-0899 number range, i did not change any thing for track YJK020

    2) It placed track no YJK001 in the number range 0020-0899 , which is wrong.


    Please change A10 in sheet 1 to YJK001 to see the error.

    Thanks.

  98. #98
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    John,

    The problem with the data I am receiving is that it does not come sorted by track number , the track numbers are randomly placed.

    The formula you posted assumes only if tracks are number sorted.

    Thanks.

  99. #99
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Sum values based on range of text AND numbers

    READ my reply carefully!

    Columns A:E are SORTED by column E so all ranges are sequential
    i.e. YOU have to sort the data!!


    PLEASE do not quote whole posts: it is a complete waste of resource.

  100. #100
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    83

    Re: Sum values based on range of text AND numbers

    This is true , It has to be sorted .

    Thanks.

+ 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. Replies: 7
    Last Post: 03-18-2016, 05:09 AM
  2. Sum Dynamic Range of Values Based on Text Style
    By DCCARGEEk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-09-2016, 03:09 PM
  3. [SOLVED] Count unique text values within a range based on another column
    By Sebastes in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-14-2014, 01:38 PM
  4. Sum values in range of text strings based on other values
    By afgoody in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-07-2013, 04:15 PM
  5. Display Text Based on Values in Cell Range
    By kythom in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-25-2013, 02:25 PM
  6. [SOLVED] Change text of one cell based on the numbers of a range of cells?
    By spookymyo in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-18-2013, 04:50 PM
  7. [SOLVED] Counting ammount of values within a range based on text
    By nonterrorist in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-07-2012, 09:16 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