+ Reply to Thread
Results 1 to 5 of 5

Reading in Number Strings with Dashes in SUMPRODUCT

Hybrid View

  1. #1
    Registered User
    Join Date
    04-22-2008
    Posts
    3

    Reading in Number Strings with Dashes in SUMPRODUCT

    I need some help regarding excel.
    Below is my data.

    Code
    Figure
    04-0070G
    509,190
    04-0090
    14,672
    04-0110
    15,000
    04-0130
    (100,000)
    04-0140
    (8,032)
    04-0150
    (80)
    04-0170
    (171,563)
    0401-C0011-51-0
    238
    0402-C0001-51-0
    145
    0403-C0001-51-3
    70

    04-1090
    (13,164)

    I need to get the total figure of items with the codes ranging from 0401 to 403.
    I tried using =sumproduct((code>="0401")*(code<="0403")*(figure)).
    Code 04-0110 to 04-0170 are also read into the data range because 04-01 is read in as 0401.
    Is there any way to rectify the problem or is there any other ways to read in the range to get the total?
    I cannot change the codes as they are fixed.

    Thank You
    Last edited by rackun; 04-22-2008 at 10:19 PM.

  2. #2
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852
    You can use a sumif formula:

    =SUMIF(A1:A5000,"*0401*",B1:B5000)+SUMIF(A1:A5000,"*0402*",B1:B5000)+SUMIF(A1:A5000,"*0403*",B1:B5000)
    Last edited by Portuga; 04-22-2008 at 11:38 PM.
    If you found the solution to your question. Mark the thread as "Solved"
    Thank everyone that helped you with a valid solution by clicking on their

    There is no such thing as a problem, only a temporary lack of a solution

  3. #3
    Registered User
    Join Date
    04-22-2008
    Posts
    3
    That won't work if I want to take in the total figure for code "04"

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    This variation will count all strings that begin with "04-"

    =SUMIF(A1:A5000,"04-*",B1:B5000)
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Registered User
    Join Date
    04-22-2008
    Posts
    3
    Quote Originally Posted by Portuga
    You can use a sumif formula:

    =SUMIF(A1:A5000,"*0401*",B1:B5000)+SUMIF(A1:A5000,"*0402*",B1:B5000)+SUMIF(A1:A5000,"*0403*",B1:B5000)
    I can't be adding on forever because the range could be a very long.
    I need something that read in a range.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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