SUMIFS et al Examples- multiple conditions

Please purchase the course before starting the lesson.

SUMIFS et al Ex 8

In this example you want to complete the matrix in E346 to I349 (yellow cells) based on the data above. Using SUMIFS and AVERAGEIFS, link the criteria and criteria ranges so that they pull from the cells in D346 to D349 and E345 to I345. Your sum in F351 should equal the amount in E342

SUMIFS et al Ex 9

In row 5 you have a row of information that groups periods into common time frames e.g. Jan to June has a 1 indicating that these all belong to the first half year, and the next 6 months have a 2 which indicates they belong to the second half year.

In D18 to G20 we want a formula that will group all the periods that belong together based on cells D15 to G15. So anything that has a one in row 5 must be summed into D18, D19 and D20 and so on.

Steps

  • Create a SUMIF in cells D18 to D20 that will add the relevant information together based on the numbers in row 5
  • Now change the numbers in row 5 so that they represents quarters e.g. Jan to March should have a 1 in row 5, April to June should have a 2 etc.

SUMIFS et al Ex 10

In this example we have calculated which bank account numbers are duplicated (column E). In column F we want to work out the instance number of the bank account i.e. the first time we see the bank account number, the second time etc.

To see the full course contents click here.

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