SUMIFS / AVERAGEIFS / COUNTIFS
Think of the SUMIFS, AVERAGEIFS, and COUNTIFS functions as SUM, AVERAGE, and COUNT with built-in filters.
We can point to a range of cells and then define criteria by which to include specific items in the range.
For example:
- Sum amounts where the “Account” is equal to “Services”.
- Average “Profit” for discounted items.
- Count “Sales” that are more than “10,000”.
You can even define multiple filter criteria, such as sum the “Sales” over “100” for “Domestic” orders.
In the below example, we want to total all “Amount” cells (D2:D25) that are associated with the “Services” account (C2:C25). The formula would appear as follows:
=SUMIFS(D2:D25, C2:C25, “Services”)
If the “Services” account were listed in a cell (ex: G3), the formula could be written more dynamically like so.
=SUMIFS(D2:D25, C2:C25, G3)
If we want to add another condition to the filter where the “Account” (C2:C25) must be “Employee Related Expenses” (G4) and the “Date” (A2:A25) must be after “1/15/2021” (H4).
We can write the formulas as follows:
To see more examples with detailed explanations of these functions, take a look behind these links:
ليست هناك تعليقات:
إرسال تعليق