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

[@altiganeymohammed]

You are Genius man God bless you

[@ddp2049]

this video was great thank you. I finish my project.

[@jsfernald]

This is exactly what I needed! Thank you!!!

[@andrebuys4814]

Awesome! Precisely what I wanted to know. This was explained in a way that is very easy to understand. Thx a mil!

[@management.gravit8SouthKlang]

TERIMA KASIH... HELP ALOT..

[@saadchaudhry9110]

thanks, i learned

[@auditorsfb450]

YOU SAVED MY LIFE

[@GeorgeAJululian]

Thank you very Useful

[@prithikayogarajah3777]

Thank you so much!

[@alexjennings02]

Thank you

[@davidmelgar1935]

Super useful!!

[@RobTyler-u9m]

awesome

[@mudassar1]

Thank you for keeping it clear and concise.

[@syedhussain6100]

If we have morethan one colum then how it will work ? because with me just working one colum as you mentioned in the video .

[@skyxcel6080]

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

[@lismar688]

What if I'd like to do the sum of two specific words?

[@JunaidAli-vc7tu]

Hey mate! I need help. I would like to create a name range for a particular "range of values". I tried to name them by "range of cells". It worked BUT when I tried to filter the table. The data changed. What do I do - if I don't want the data to change when I filter. Let me know how to proceed. Thank you.

[@skyxcel6080]

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.

[@JunaidAli-vc7tu]

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.

[@markofilipovic973]

Hi Is possible to sum all WA11? (A1) WA11 4 (A2) AdBlue 1, WA11 223 (A3) AdBlue 3, WA11 32, shift 4 ... and everything is in one column.

[@skyxcel6080]

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

[@brcleaningcompany2566]

what if it contains time and cost in same cell but i only want to sum the currency in each cell on the column,,, example (A1) "bob 3:00 oclock $450" (A2) "julie 5 o clock $20" how do i sum only the cost? when the description and time and cost is all in one cell? any assistance would be greatly appreciated

[@skyxcel6080]

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!

[@kimprado2305]

does this work horizontally?

[@skyxcel6080]

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

[@fosterchild4523]

Great video...I need help man.....I am trying to do something very very very simple but I can't seem to figure it out. I have two columns of data. One column A with sentences and one column B with numbers. All I want to do is use sumif() to search column A for a specific string and add the numbers in column B together every time it finds an instance of my specific string in column A. But.........the problem is that I can't figure out how to make it look for my string specifically........example.....If I want to search column A for the string "pot"......I want to find every instance of the word "pot"......I don't want to include "pots"......"potted"....."spot"....."spotted"....etc.........for the life of me I can't figure it out.

[@skyxcel6080]

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!

[@fosterchild4523]

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

[@skyxcel6080]

@@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!

[@fosterchild4523]

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

[@meranism673]

Thank you for the information sir. I would like to see if there is a way to apply the same method for several columns? I have a sheet where I input my monthly expenses and I separated it by months. So each month has it's own expenses in different column. Is there a way to get a sum for a specific expense, let say (GAS)? I would really appreciate it if you could help me. Other wise the way I do it now I get a sum for each month and then add up all those numbers to get a sum of all. That's taking a long time to put the same equation for each month over and over again. =SUMIF(A3:A25,"*"&AI5&"*",B3:B25)+SUMIF(C3:C25,"*"&AI5&"*",D3:D25) - this is what I use. but I have to do it for 12 months. Is there a shortcut for achieving the same result?

[@skyxcel6080]

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!

[@meranism673]

@@skyxcel6080 Thank you for your response .

[@pm7715]

How can I do this formula to sum without a letter being in front of the numbers? Please help.

[@skyxcel6080]

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!

[@pm7715]

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

[@andyfreeman6942]

Heeeeeeeelp? How do I sum a range of cells values when the cells contain text too? The text is not secific

[@hammadraza5414]

Hi, What if we have a table and we want the total of all the calls contain specific text in whole table, not only in a row?

[@skyxcel6080]

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!

[@syedhussain6100]

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

[@yaledioma00]

how can i sum text?

[@YanKanaan]

what if i want to match the value of 2 cells and not just 1 cell?

[@WormholeVenue]

How can i add up a long row, but only add up the values that are formatted as dollars? I tried your formula with "*$*" to only pick cells with a dollar sign on them. That didn't work.

[@skyxcel6080]

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!

[@abdulhamidalfani]

hi, im not sure why it wont work if this formula is inserted inside a long formula..

[@skyxcel6080]

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

[@parthasarathybalasubramani6148]

WHAT IF THERE IS A MERGED CELL BETWEEN THE QUANTITY ROW AND YOU HAVE CONTINUE THE ADDITION? ANY OTHER TIP OR DO WE HAVE TO REPEAT THE SUMIF ?

[@skyxcel6080]

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!

[@zegheminam2480]

Can you help me? HSPW-0000 this is employee number P= Philippines & W= worker. Please tell me formula that I write employee ID and get its Nationality

[@skyxcel6080]

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!

[@sureshv3070]

How to set Item ( ie.f4)

[@skyxcel6080]

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!

[@enriquevalencia678]

LOL I'm just trying to see how many S1, S4, MCCS, MCG and S6 are in ONE singular column. Great video by they way but still lost.

[@jhanolaer8286]

how to sum like this in one cell (10kg x 10mtrs)+ (2kg x 30mtrs) and the answer should like this (12kg x 40mtrs) 😊

[@skyxcel6080]

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!