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

1. ## 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.

Warm Regards
e4excel

2. ## 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

3. ## 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. ## 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. ## 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....

6. ## 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. ## Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

Try this array formula
Formula:
`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

8. ## 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. ## Re: Summation of Numbers with Text & Numbers in Multiple Cells with Multiple Criteria...

I think I missed the "Ongoing" in my previous post
Formula:
`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. ## 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. ## 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.

12. ## 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 ?

13. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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

21. ## 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. ## 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.

23. ## 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. ## 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...

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

Thanks John....

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

#### 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