Sumifs and wildcard condition on column contents
The most used formula in excel is SUMIFS, which, in my opinion, together with a wildcard condition, is the element which supports and ensures the existence and usage of excel application.
Let’s imagine we have a spreadsheet which contains 2 columns: first one shows the sales revenues for a certain period and the second shows the product names and sizes (concatenated like this: product1 size 10, product2 size 12 etc).
We need to find out the structure of the sales revenue based on the size of the products.
On the reporting spreadsheet we need to show all the sizes: size 10, size 11, size 12 etc vertically. Near each size we should use the following formula: =SUMIFS(revenue column, product-size column,”***”&sizecell)
If the size was in the middle of the string, we could have used wildcard also after the size sub string.
This formula uses a wildcard *** to add up all the revenues which have size x, without taking into account the product name.