SUMIFS et al Examples- partial matches

Please purchase the course before starting the lesson.

SUMIFS et al Ex 11

In this example we have a listing of job descriptions that aren’t standardised. We are trying to count how many people match the general descriptions in D4 to H4.

  • In D5 to H5, count how many people have the descriptions in D4 to H4 in their job title/ description
  • In D6 to H6, count how many people DON’T have the description in their title
  • In D9 count how many people have General Labour but NOT General Labour Supervisor in their description
  • In E9 count how many people have the word Skilled in their description ignoring the Semi Skilled and Skilled Supervisor

SUMIFS et al Ex 12

In column H we have a partial lookup to pull through the selling price of a product based on a shortened version of the name that the market knows them as. The problem is that some shortened versions may be a match for more than one product.

In I10 to I34, count how many matches there are for the shortened name.

SUMIFS et al Ex 13

In this example we want to pull through the employees matching email address. However, the 2 systems use different versions of a name. HR has separate columns for First Name and Surname while IT have a combined Initial and Surname.

In D4 column we have used a partial VLOOKUP to pull through the email address, but in E4 column we want to build a count to see how many matches there would have been for that surname (have a look in the VLOOKUP function to see what it is looking at).

To see the full course contents click here.

Lesson tags: vlookup
Back to: Advanced Excel Course > SUM, COUNT, AVERAGE IFS