+ Reply to Thread
Results 1 to 26 of 26

Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

  1. #1
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,327

    Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    Dear Forum,

    I need to sum a Column Range where each cell or Row has Text with Numbers and I need to only sum those cells with a specific text..The typical text in these cells would be as mentioned below:

    Please Login or Register  to view this content.
    As can be seen above, I need to only Sum those cells where the Text is "Ongoing SIP *" so my answer for the SUMMATION would be Rs 10,000 as there are just 4 entries with the "Ongoing SIP" prefix...

    I have got a solution to get the number from each cell , however I need to also Sum these numbers only for specific cases.

    The Solution is as mentioned below..

    Please Login or Register  to view this content.
    My Data Range is from BI2:BI27 which I have defined a name as "Status"

    So without using a Helper column, can this be achieved.

    Thanks in advance.

    Warm Regards
    e4excel
    Last edited by e4excel; 09-13-2017 at 11:10 AM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,861

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    Hi e4excel,

    I'd do this problem using Text to Columns, then sort the data to remove blank rows then a SumIf() function. If you want to do more with the data, I'd put a header on each column and Pivot the Data after Text to Columns.

    Sumif after Text 2 Columns.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,327

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    Thanks but I would prefer a Formula if possible as I will be automating this file as Values...Advanced SUMIF with Specific Criteria ans Summing Numbers coerced into TEXT....
    I would appreciate if it's possible, then this solution would be really useful in many places with such requirements in my file...

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,861

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    OK e4excel,

    Try this monster using CSE to enter the formula...

    =SUMPRODUCT(--(LEFT(A1:A30,1)="O"),IFERROR(VALUE(MID(A1:A30,26,5)),0))

    See the attached that works on your data. If you have more than 30 rows you will need to change the A30 to A300 to span your data.

    CSE SumProduct with criteria.xlsx

  5. #5
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,327

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    Thanks Marvin for offering a formula solution but it isn't working in my file, please also explain me the 26 and 5 in the formula....I will be putting this formula below the Rows like how we usually put the SUM below all the Rows...

    Ok those are for the MID formula....
    Last edited by e4excel; 09-13-2017 at 12:24 PM.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,389

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    The 26 is the starting position of the RS value in the text string and 5 is the length. If either of these differ, you will not get a correct result.

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    Try this array formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ***Array formula
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
    You will know the array is active when you see curly braces { } appear around your formula.
    If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    the result should be 31500
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  8. #8
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,327

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    My Range was BJ2:BJ27...

    I have a formula in the above range and these are not values so is that the reason that none of the formulas are working....

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    I think I missed the "Ongoing" in my previous post
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ***Array formula
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
    You will know the array is active when you see curly braces { } appear around your formula.
    If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  10. #10
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,327

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    I am sorry AlKey its still not working I converted the formulas into values changed the Indent Level to 0 too but nothing seems to work..
    I presume that we are Hardcoding the Formula based on the different text in the cells but is it possible to have such a formula which focuses only on the "Ongoing Sip" as a Search Criteria and the Range...I am planning to even Sum the "Stopped SIp"

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    I don't know why you saying that it's not working.
    Here is an attached file.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,327

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    My Sincere Apologies , I had removed the word "Amount" and did not realise that I had mentioned that in the original file..So sorry for that trouble, but let me ask one thing can we have a formula which just extracts the number 2000 from the sentence wherever the "Ongoing*" appears..

    It indeed is working now that I checked...after putting Amount again...

    I was hoping to have a formula which does not have hard-coded numbers for the Text Functions is that possible ?

    Thanks in advance......

  13. #13
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    Please be specific. Before you wanted to SUM up now you saying "just extracts the number 2000". Theses are different things? Or do you need to sum values that =2000?

  14. #14
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,327

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    No i want to SUM THE values and the values can be of any length like it can be even 500, 1000, 5000, 5500, 10,000, 15,000 hence I mentioned the word extracting the number in the sentence...Believe me it took me time to understand the MID function and I was re-viewing the solutions provided by you and MarvinP..

    In my first thread I had mentioned one solution to just extract the number 2000 the 2000 is just number in that scenario, however I needed some formula in the same lines which could pick any number
    Please Login or Register  to view this content.
    I dont know how to make the above formula to also SUM up for Number of ROWS as well as for a specific criteria...

  15. #15
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,861

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    Again, the text to columns answer may still be the best.

  16. #16
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,861

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    Hey e4excel,

    If you would have put in different lengths of the amounts like: Rs 50, Rs 3,000, Rs 45,000 and attached a sample file it would have made your problem a lot more understandable for us. After you didn't like the Text To Columns answer, my second formula used your exact values (in your original thread example) which all had 4 digit numbers and a comma in the number. My answer that used Mid(.., 26, 5) was picking out the number from the string, as you gave us a poor example of your data. Of course it would fail with longer or shorter numbers, but that wasn't what you asked for us to solve.

    If looks like you now want a formula using Lookup and a fixed array using Min(Search()) as you've shown above.

    What about the above formula, that you give, doesn't work? Why don't your like AlKey's formula? Would a helper column work for you? Can you supply a better example in an attached workbook?

  17. #17
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,327

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    All your formulas worked when I added the word Amount, that was my mistake as I decided to remove that earlier and then did not realize my folly..
    But the Amounts are different amounts of Investment...

    Believe me I presumed that the LOOKUP approach which is not hard-coded gave the result for an individual cell, so I thought maybe a similar approach formula would be possible but now after looking at your solutions I see the challenge for Summing the formula needs to be having the same width I guess..

    So I will have to use your approach if its not possible , please tell me if there's even an iota of possibility to have a formula

  18. #18
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,861

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    Hey,

    Once again, a formula is possible based on a good example. Will ALL the numbers follow the test "Rs"? Seeing a good example of say 100 samples lets us create a formula based on real data. If you can supply the sample, we can try to supply an answer for you. You need to attach a sample file and we can help then. Without a good sample file, we are still all guessing.

    Do you need instructions on how to attach a sample file?

  19. #19
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,327

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    It will take me some time to add a file as i need to download a different file for each month from an Investment website but the Rs would be always there and the amount would always change..
    I am using the below Rupees format -
    Please Login or Register  to view this content.
    I may make some changes in the text like "Ongoing SIp of Rs 2000 " removed the word amount..

    Final Data would be like this...


    Please Login or Register  to view this content.

  20. #20
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,861

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    Hi,

    Looking at your data above, all the amounts are exactly 5 characters long. In an earlier post you said some would be longer and some smaller. Your example above doesn't reflect that....
    If I use your above example then this formula entered as a CSE will work.

    Please Login or Register  to view this content.
    BUT - because I know (you said earlier) that some of the numbers might be 500 or 1,000 or 10,000 then try this formula using a CSE enter.

    Please Login or Register  to view this content.
    Try the above formula and let me know what happens...

    See my new example and formula in:

    CSE SumProduct with criteria 2.xlsx
    Last edited by MarvinP; 09-13-2017 at 05:44 PM.

  21. #21
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,327

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    Thanks a lot MarvinP and AlKey for your help, finally I have got the solution for this query....But just a query can we have something not to hard-code the numbers 26,5,50,200,50 etc...
    I would like to confirm that this is working great for me but I wanted it to be Dynamic..

  22. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,389

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    I would like to confirm that this is working great for me but I wanted it to be Dynamic..
    So what does "dynamic" mean ... as the formulae given are effectively "dynamic" by the use of the SUBSTITUTE function.

    Unless you last posted sample is NOT typical, the formulae will work without change.
    Last edited by JohnTopley; 09-14-2017 at 02:48 AM.

  23. #23
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    @e4excel
    Those formulas I suggested earlier were already built to be dynamic to accommodate any number from 1 to 15 (15 significant digits is a maximum in Excel).

  24. #24
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,327

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    I meant by Dynamic , if I added any other text then would it still work..
    Anyways, its worked for me so I am really about these solutions..

    Thanks once again

    How do I mark this thread as SOLVED...
    Last edited by e4excel; 09-14-2017 at 12:38 PM.

  25. #25
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,389

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

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

  26. #26
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    2,327

    Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

    Thanks John....

+ 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: 2
    Last Post: 05-26-2015, 07:29 PM
  2. Sum of text cells with multiple criteria
    By Cbrez in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-07-2014, 12:20 AM
  3. Replies: 4
    Last Post: 07-21-2014, 10:33 AM
  4. Replies: 3
    Last Post: 12-12-2012, 04:01 PM
  5. Count multiple rows (text) with multiple criteria (text)
    By alecabral08 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-21-2008, 04:26 PM
  6. Summation of cells with mixed numbers & text
    By HTWingNut in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-31-2007, 10:31 AM
  7. Sum multiple cells with different numbers and text.
    By chrisjwhite24 in forum Excel General
    Replies: 1
    Last Post: 06-30-2006, 12:10 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