• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumif for multiple text in a cell

Dear friends,

In a cell I try to type multiple words separated by commas. I would like to retrieve the summation for those words.

I have attached a sample file hope it would help you to understand clear. I am happy to have a formula based solution as well.
 

Attachments

  • pavan3.xlsx
    10.8 KB · Views: 7
{=SUM(SUMIFS(B3:B6,A3:A6,J2:K2))}
will work if entered as an array formula (ctrl + shift + enter) (when copying, do not copy the curly braces)
It pulls the gbg codes from J2 and K2. If you had a list of codes somewhere, then it would sum all the sign values from column b, where the gbg code in column a is one of the codes in your list range. Is this what you wanted?

It works in the same way as your formula in E3, but instead of you manually creating the array within the formula, you just give it a range, and enter it as an array formula, and it runs the sumifs once for each item in the range, and adds all those values together.
 
If the criteria are always in row 2 of the same column as the formula, define a name called Criteria using:
=EVALUATE("{""*"&SUBSTITUTE(INDIRECT("R2C",0),",","*"",""*")&"*""}")
and then enter:
=SUM(SUMIF($A:$A,Criteria,$B:$B))
in F3 using Ctrl+Shift+Enter to array enter it.

If the criteria are just in the cell above the formula cell, use this for the name definition:
=EVALUATE("{""*"&SUBSTITUTE(INDIRECT("R[-1]C",0),",","*"",""*")&"*""}")
instead.
 
If the criteria are just in the cell above the formula cell, use this for the name definition:
=EVALUATE("{""*"&SUBSTITUTE(INDIRECT("R[-1]C",0),",","*"",""*")&"*""}")
instead.
Nice.
 
Hi Pavan,
You've wrongly used wildcard, therefore all values are getting summed.

Now you have various solutions, try this if you are interested in wildcard solution:
=SUM(SUMIF(A3:A7,{"*0CSG","*1JTU"},B3:B7))

or this with SUMPRODUCT:
=SUMPRODUCT((B3:B7)*((A3:A7=J2)+(A3:A7=K2)))

Both are regular.

Regards,
 
Dear Fellas,

All your formulas were very helpful. I think due to my problem, I did not communicate the problem statement clearly.

Let me try in this post..

Column A and B is a database which has around 100K rows of data which may not go extensive.

Cell F2 is output of a query which I get it internally from the reports.

Cell F2 varies second by second.. like a random numbers which picks from column A of a database.. sometime it pick Cell A3, A4 & A5 or first 10 records or last 10 records or random 5 cells and so on... (BUT IT PICKS THE UNIQUE VALUES FOR COLUMN A)

What I do manually is based on the F2 cell I do text to columns to separate with comma delimiter to separate the records and apply sum if to all the individual records.

Is there any better way through formula or a vba way to eliminate the text to columns option and arrive the summation based on the F2 cell data.

Regards,
Pavan S
 
Debaser,.. IT WORKS LIKE A CHARM.. This is what I wanted..
could you please help me understand the criteria.. (evaluate function) plz.....

I am loving it.. thanks for your time dear.
 
Your cell contents (value1,value2 for example) are converted to the text {"*value1*","*value2"} and EVALUATE then interprets this as if you had entered it into a cell - i.e. as an array constant - and returns an array of those values. That is passed to the SUMIF formula.
 
Hello Debaser,

Got it.. But another question is. why would it ask us to save the file in xlsm format? and the formula would not work on .xlsx format
 
The EVALUATE function is an old XLM macro function, which is why you have to use the xlsm/xlsb or xls format.
 
Back
Top