Given the abundance of software choices for businesses nowadays, Excel might appear less essential. Nonetheless, there may come a time when Excel becomes necessary to retrieve precise data. This is why many software programs offer the option to export data to Excel. Today, I will share a SUMIFS formula that I have utilized to streamline data analysis and present it in a desired layout
Take the data below as an example of payments from different locations.
Let's say we want to sort these payments based on date, payment type, and location. We can do that by setting up a table that looks like the following:
Once we set up our table, we can add our formula. This formula is a SUMIFS formula allowing you to set the data you want to be totaled based on multiple criteria. You will see below that the formula allows you to select a sum range first, then a criteria range, followed by the criteria you want to search for within the range.
In our case we want the formula to basically say, "search for any of this payment type that took place at this location on this day. When we do so, our formula will look like this:
The formula is performing the action we described earlier. It is totaling any amounts that match a specific payment type, location, and date. You will see I started by entering my formula in cell J2 and then added my formula to all of the other cells by dragging it across then down. The dollar signs in my formula serve to maintain its integrity when I move it to various cells, and I have provided a description below detailing the purpose of each variation. I highly recommend doing this as it saves you from having to change the formula in each cell and it locks your criteria in place so you know you are getting the correct results.
Once you have entered your formula into all the cells, you will get the results below if everything is correct. I always recommend spot checking your data to ensure your formulas are working as you intended.
To sum up, Excel offers numerous possibilities to achieve the desired outcome. The method I have shared is just one of the ways I utilize Excel and find valuable. In addition to pivot tables, you can also incorporate other formulas, which I intend to discuss more in future posts. Stay tuned for more Excel tips in the future.
Comments