![]() SUMIF requires a range for the range argument. Instead, the colors exist in an array returned by the TEXTBEFORE function. Note: we can’t use the SUMIF function in this case (which would be easier) because we don’t have the colors without numbers in an actual range. To sum the numbers associated with each color in column F, you can enter this formula in cell F8 and copy it down: The array is returned to cell E8, and spills into the range E8:E12. This array is returned directly to the UNIQUE function, which returns an array with 5 unique colors: TEXTBEFORE returns an array of 12 colors like this: Instead of using TEXTAFTER, we are using the TEXTBEFORE function to get the text before the space (" “): This is a variation of the formula explained above. To list the unique colors starting in cell E8, you can use: One easy way to create the summary table seen in the worksheet is to use two formulas, one to list unique colors, and one to sum the numbers associated with each color. This array is returned directly to the SUM function: The result is an array that contains numbers only: To coerce these text strings into actual numbers, we use a double negative (–): Notice that at this point the numbers are still text, as you can see by the double quotes (”). ![]() Because we are giving TEXTAFTER 12 separate text strings in data (B5:B16), TEXTAFTER returns 12 results in an array like this: ![]() TEXTAFTER is configured to extract the text that occurs after a single space character (” “). Working from the inside out, the TEXTAFTER function is used to extract the numbers like this: To sum all the numbers that appear in B5:B16, ignoring text, the formula in E5 is: See below for more details and for the formulas used to generate the summary table in E7:F9. Where data is the named range B5:B16, and each text string includes both a label and a number separated by a single space (" “). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |