If there happen to be multiple rows with the same class and accounts, then the SUMIFS function would return the sum of all matching items.Īs you can see, if the value you are trying to return is a number, then the SUMIFS function makes it simple to perform multi-column lookups. Thus, to populate our report, we’ll retrieve the amount values from the export, and match the class and account columns, as shown below. It is helpful to think about the function in these terms: add up this column (argument 1), only include those rows where this column (argument 2) is equal to this value (argument 3), and where this column (argument 4) is equal to this value (argument 5), and where…and so on, up to 127 pairs. The remaining arguments come in pairs: the criteria range and the criteria value. In our case, the column that has the value we wish to return. The first argument of the SUMIFS function is the sum range, that is, the column of numbers to add. If we apply this idea to our task at hand, we would quickly realize that we could use this conditional summing function to retrieve our report values. This multiple condition summing function is designed to add up a column of numbers, and only include rows that meet one or more conditions. Beginning with Excel 2007, Microsoft included the conditional summing function SUMIFS. If you are trying to retrieve a numeric value, such as an amount, then a traditional lookup function may not be your best bet. It depends on what you are trying to retrieve. And, lookup tasks are best solved with traditional lookup functions…right? Well, it depends. If you are familiar with the VLOOKUP function, it feels natural to try to build the report with this function because, after all, this is a lookup task. We want to retrieve the amounts and place them into our little report, pictured below: A sample of the export is shown below:įrom this exported data, we would like to retrieve selected amounts based on the class and account columns.
We have exported some information from our accounting system, and it is basically summarizes the transaction totals for the month by class and by account. Multi-Column Lookup Objectiveįirst, let’s confirm our objective by looking at a sample workbook. Fortunately, there is another function that may work as an alternative to VLOOKUP depending on what you want to return. If you have ever tried to use a VLOOKUP function with two or more criteria columns, you’ve quickly discovered that it just wasn’t built for that purpose.
0 kommentar(er)
