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.

how to use sumifs and wildcard

This formula uses a wildcard *** to add up all the revenues which have size x, without taking into account the product name.

 

Author has written 295 articles

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.