SUMIF et al Examples 1

Please purchase the course before starting the lesson.

SUMIFS et al Ex 1

In B11 to F26 we have data per sales person. In C32 to C34 and F32 to F34 we want to summaries the Goals and Sales based on the business description.

In C35 and F35 add up the totals and check that they match to the totals in C27 and F27.

SUMIFS et al Ex 2

In this example you want a summary of students bills based on the monthly amounts outstanding.

  • In B8 column, use COUNTIF to calculate how many times the student number in A8 appears in the table of B24 to B48.
  • In C8, D8 and E8, use SUMIF to add up all the relevant bills for the student by extracting it from the database below.
  • Check that your answers match up.

SUMIFS et al Ex 3

You have 2 lists on your spreadsheet with transactions. Although they should be exactly the same you have discovered each list is missing some information that is contained in the other list. You need to quickly determine which transactions are not common or repeated too many times.

Steps

  • In Column G, create a formula that COUNTS IF the transaction ID as per column B, also exists in the second list (column L)
  • In Column Q, create a formula that COUNTS IF the transaction ID as per column L, also exists in the second list (column B)
  • Using the autofilter, check each list to see the items that are not common or appear too often.

To see the full course contents click here.

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