How to Sum Values If a Cell Contains a Specific Text | SKYXCEL 

Hello, are you referring to having multiple columns for the criteria or multiple columns to sum?

Hello, my apologies for the delayed response. I think it really depends on how your spreadsheet is set up. Exp Jan Exp Feb House 1800 Gas 160 Gas 150 Food 400 Phone 50 Water 75 If it is set up like this then what you are doing is correct. Exp Jan Feb House 1800 1800 Gas 150 200 Phone 50 50 If you can set it up like this where you only write the expense once, you can use this formula: =SUMPRODUCT((A2:A4="Gas")*(B2:C4)) A2:A4 being the range you want evaluated and B2:C4 being the range you want to sum. This is just me assuming how your spreadsheet is set up, so my answer may not be sufficient for your purpose, but if you would like to provide any extra details I will be happy to assist!

@@skyxcel6080 Thank you for your response .

Hello, I am not sure what you have in your formula, but if you are using the wildcard symbols "*" around the string "pot", e.g. "*pot*", that is most likely the cause of incorrect results. The formula should be as follows: =SUMIF(A1:A5,"pot",B1:B5). This will only sum the cells in column B that just have the string "pot" in column A. Please let me know if you have any further questions!

@@skyxcel6080 Thank you for the reply. Yes, that does not work for my application...Column A1:A5 has sentences in it like "succulent pot", "3 inch pot for plants", "white planter pots", "small plants for potting". And I cannot simply put "pot" in my SUMIF formula because It will then look for cells with ONLY the word "pot".....so, if I put "*pot*"....that does find every instance that "pot" is used...but it will also include "pots" and "potting" which I do not want. I have found a very cumbersome workaround though....I can use =ISNUMBER(FIND(" pot ", " "&A1:A5&" "))......this will look for ONLY the word pot and return a column with TRUE or FALSE values in it....I can then use this "helper" column with SUMIF and sum up all of the values in my number column where the corresponding column is TRUE.....this works very well......BUT sucks.....and it seems like the solution should be very very simple.

@@fosterchild4523 Ahh I see. I would be careful with your current formula because you are looking for "pot " and if "pot" is used at the end of the string with no space following it Excel will not catch that. Try this formula out: =SUM(SUMIF(A1:A4,{"*pot *","* pot"},B1:B4)) For some reason this comment is removing some of my (*) marks, so I will wrap them in parenthesis (*), but remove them once you get the formula in Excel. =SUM(SUMIF(A1:A4,{"(*)pot (*)","(*) pot"},B1:B4)) This formula looks for two instances "pot " with a trailing space and " pot" with a leading space. Give this a shot and let me know how it goes!

@@skyxcel6080 Nailed it....that works perfectly....thank you very much....saved me tons of work.

Hello, I would be happy help, but I think I need to get some more clarification on the situation. In what way did the data change? Also, did you get an error when trying to use "range of cells" as the name of the range? Reason why I am asking is because spaces are not allowed to be used in a named range.

sure, please provide your email address. Will let you know the entire situation with the work excel file. I guess that would make it easier. Thank you.

=SUMPRODUCT((--(ISNUMBER(FIND("WA11",A1:A3)))))

Great question! What I would do in that particular situation is create a helper column to extract the dollar amounts into a separate column then sum up the helper column. Going along with your example, in cell B1 insert this formula: =NUMBERVALUE(RIGHT(A1,LEN(A1)-FIND("$",A1))) This formula extracts all the characters to the right of the "$" sign and converts it into a number. Copy this formula down to cell B2 and then add a simple SUM formula to total up the column: =SUM(B1:B2) I hope this helps and please let me know if you have any further questions!

Hi Hammad, for this particular scenario I would suggest using the COUNTIF function. The only two arguments this function needs is the Range, which are the cells you want evaluated and the Criteria specifying the condition. Ex: =COUNTIF(Table Range, "TEXT"). Remember to use the "*" mark as a wildcard before and/or after your text value if you are looking for a string of text at the beginning and/or the end of a text value. Please let me know if you have any more questions, thank you!

@@skyxcel6080 Hi, If we have morethan one colum then how it will work ? because with me just working one colum..

Hello, I will gladly provide you with some assistance, but could you explain in a little more detail as to where this merged cell would be located in reference of the table in the video? Thanks!

Hello, yes this will work for horizontal ranges as well!

Hello, can you copy and paste the entire formula you are trying to create?

Hello, In this example, let's use the first item # in the video "S101A". For your scenario, without the letter in front it would be "101A". If we were still trying to sum the quantity for any string of text that contains "101" then all we would have to do is remove the first asterisk mark (*) in the equation like so: =SUMIF(C4:C11, "101*",D4:D11). If there are no trailing numbers or letters following "101" then we would need to remove both asterisk marks because there are no characters before or after the sting of text that we are searching for. I hope this helps and please let me know if you have any further questions. Thanks!

@@skyxcel6080 I want to thank you very much. This was very helpful.

Hello, great question. It was a good thought to try to use "*$*", but the reason why that did not work is because the $ is not actually part of the value, it is just how the cell is formatted. For example, if you click on a cell that is formatted as a Currency (e.g. $10) you will only see 10 in the formula bar without the $ sign. There is really no great way to get around this, but we have a few options. Option 1: Create a function or macro with VBA. Option 2: Create a helper row to specify the format of a specific cell. EX: Let's say in Row 1 you have 5, $10, 15, $20 In Row 2 (the helper row) you can use this formula to specify the cell formatting: =CELL("format",A1). Now in Row 2 you should see G, C0-, G, C0- Then in your output cell where you want to sum all the cells that have a Currency format you can use this formula: =SUMIF(A2:D2,"C0-",A1:D1) The total should be 30. I hope this helps and please let me know if you have any further questions!

Hello, you can type in any string of text in that cell if you wanted, but to set it up like it is in the video then you will have to use Data Validation. 1. Click the Data tab 2. Select Data Validation 3. In the "Allow:" drop down menu select "List" 4. In the "Source:" box you can either type 101, 201, 301 or if you have a range of cells in the worksheet that contain the values you want to include in the drop down list then you can just click inside the "Source:" box then highlight the range of cells. 5. Click OK I hope this helps and please let me know if you have any further questions!

Hello, if "HSPW-0000" is in cell A1 then a basic formula would be: =IF(FIND("P",A1),"Philippines"). This formula states that if the letter "P" is found within the text in cell A1 then output "Philippines". However, this formula may only work for certain situations, if there is another employee number that contains the letter "P", but has a different meaning then this formula will not work. Depending on how many different forms the employee numbers can be in the formula may need to be tweaked to account for different scenarios. If the nationality is always defined by the 3rd character in the string I would suggest setting up a nationality key in a table form with letters in one column and the associated nationality to that letter in another column like so: (Column D, Column F) E, English F, French G, German Then use the MID function to extract/look at the 3rd character of the string and combine the VLOOKUP function to output the nationality associated to that letter using the table: Employee Number (cell A1) = HSFW-0000 Formula: =VLOOKUP(MID(A1,3,1),$D$1:$E$3,2,0) = French I hope this helps or gives a sense of guidance to an appropriate solution. Feel free to reach out for any more questions!

Okay this one is a little tricky, so I am going to split it up into pieces stating what each part of the formula does then combine it all into one formula. So in cell A2 we have: (10kg x 10mtrs)+(2kg x 30mtrs) Formula to find the number between the 1st "kg" and the 1st "(": =MID(LEFT(A2,FIND("kg",A2)-1),FIND("(",A2)+1,LEN(A2)) Formula to find the 2nd instance of "(": =FIND("(",A2,FIND("(",A2)+2) Formula to find the number between the 2nd "kg" and the 2nd "(": =MID(LEFT(A2,FIND("kg",A2,FIND("(",A2,FIND("(",A2)+2))-1),FIND("(",A2,FIND("(",A2,FIND("(",A2)+2))+1,LEN(A2)) Formula to find the number between the 1st "mtrs" and the 1st "x": =MID(LEFT(A2,FIND("mtrs",A2)-1),FIND("x",A2)+2,LEN(A2)) Formula to find the 2nd instance of "x": =FIND("x",A2,FIND("x",A2)+2) Formula to find the number between the 2nd "mtrs" and the 2nd "x": =MID(LEFT(A2,FIND("mtrs",A2,FIND("x",A2,FIND("x",A2)+2))-1),FIND("x",A2,FIND("x",A2,FIND("x",A2)+2))+2,LEN(A2)) Then last but not least we will combine all the formulas that are used to find the numbers between 2 specific characters while adding the VALUE function so we can sum text values, as well as, concatenating some text values to get it back into the same format: ="("&SUM(VALUE(MID(LEFT(A2,FIND("kg",A2)-1),FIND("(",A2)+1,LEN(A2))),VALUE(MID(LEFT(A2,FIND("kg",A2,FIND("(",A2,FIND("(",A2)+2))-1),FIND("(",A2,FIND("(",A2,FIND("(",A2)+2))+1,LEN(A2))))&"kg x "&SUM(VALUE(MID(LEFT(A2,FIND("mtrs",A2)-1),FIND("x",A2)+2,LEN(A2))),VALUE(MID(LEFT(A2,FIND("mtrs",A2,FIND("x",A2,FIND("x",A2)+2))-1),FIND("x",A2,FIND("x",A2,FIND("x",A2)+2))+2,LEN(A2))))&"mtrs)" This is the final formula you will want to use, but the others above help you break it out and see which each one is doing. I hope this helps and please let me know if you have any further questions!