<h2>SUMIFS et al Ex 11</h2> 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. <ul> <li>In D5 to H5, count how many people have the descriptions in D4 to H4 in their job title/ description</li> <li>In D6 to H6, count how many people DON'T have the description in their title</li> <li>In D9 count how many people have General Labour but NOT General Labour Supervisor in their description</li> <li>In E9 count how many people have the word Skilled in their description ignoring the Semi Skilled and Skilled Supervisor</li> </ul> <h2>SUMIFS et al Ex 12</h2> 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. <h2>SUMIFS et al Ex 13</h2> 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 <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

<h2>SUMIFS et al Ex 8</h2> 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 <h2>SUMIFS et al Ex 9</h2> 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 <ul> <li>Create a SUMIF in cells D18 to D20 that will add the relevant information together based on the numbers in row 5</li> <li>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.</li> </ul> <h2>SUMIFS et al Ex 10</h2> 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 <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

<h2>SUMIFS et al Ex 4</h2> In this example you are sending a file off to the bank to make the salary payments. However, you just want to check that all the bank account numbers are unique in case of finger trouble or worse phantom employees. Using a COUNTIF, in E7 column, create a formula that will check whether the bank account number in column B for the employee appears as anyone else’s bank number as well. <h2>SUMIFS et al Ex 5</h2> In our example we have a dump of the cash flow report from a system. You will notice that SUMIF a and SUMIF b have the same information but they are not on the same lines e.g. the Total Closing Bank Balance on SUMIF a is on row 38 and for SUMIF b it is on row 31. We want to determine the ratio of receipts to payments on row 55 but would normally have to create the formulas on each sheet to take into account the row numbers being inconsistent. We want formula in rows 46 to 51 that will search through the information and pull out the information we need. Once we have the information static, we can then run the ratios. Steps <ul> <li>On SUMIF a create a formula in D46 to J51 that will find and add the items described in C46 to C51 for each period.</li> <li>Once you have create this you should be able to copy and paste these formula into SUMIF b (same area) and it will work even though the rows with the relevant information are not identical.</li> </ul> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

<h2>SUMIFS et al Ex 6</h2> In this example we want to automatically count how many months the Gross profit is negative and how many months are positive <ul> <li>In E13, use COUNTIF to count how many numbers in row 10 are negative numbers. In this case the criteria needs to be in quotes e.g. “<0”</li> <li>In E14 use COUNTIF to count how many numbers are positive</li> </ul> <h2>et al Ex 7</h2> In this example we want to avoid a ‘unique’ number being used twice. <ul> <li>In Column G (yellow cells), create a formula that will check if Transaction ID (column B) for that row, is duplicated somewhere else in column B (that is the invoice number has been entered twice accidentally)</li> </ul> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

<h2>SUMIFS et al Ex 1</h2> 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. <h2>SUMIFS et al Ex 2</h2> In this example you want a summary of students bills based on the monthly amounts outstanding. <ul> <li>In B8 column, use COUNTIF to calculate how many times the student number in A8 appears in the table of B24 to B48.</li> <li>In C8, D8 and E8, use SUMIF to add up all the relevant bills for the student by extracting it from the database below.</li> <li>Check that your answers match up.</li> </ul> <h2>SUMIFS et al Ex 3</h2> 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 <ul> <li>In Column G, create a formula that COUNTS IF the transaction ID as per column B, also exists in the second list (column L)</li> <li>In Column Q, create a formula that COUNTS IF the transaction ID as per column L, also exists in the second list (column B)</li> <li>Using the autofilter, check each list to see the items that are not common or appear too often.</li> </ul> To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/advanced-excel-course/">here</a>.

SUMIFS et al Examples- multiple conditions, duplicate bank account numbers

SUMIFS et al Examples- count profitable months, stop duplicate entry

SUMIFS et al Examples- sales by area, recon lists, duplicate bank accounts