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

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

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

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

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

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.

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

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?

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

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?

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?

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

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.

@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).

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