Just adding a few numbers without any condition applied is a very rare scenario.
Most of the time, we may face situations where we are required to filter the data out first and then sum them up…!!!
Let us see how to use it in detail…
1. What is the SUMIFS Function?
2. What is the Syntax?
3. How to use it with an example?
1. What is the SUMIFS Function?
SUMIFS function can be used to add the number of values based on multiple conditions or criteria, which can be passed as arguments.
2. What is the Syntax?
=SUMIFS(sum_range,criteria_range1,criteriae1,[criteria_range2, criteria2],…)
- sum_range = (Required) A number, a range of cells which contain numbers or a single cell which contain a number.
- criteria_range1 = (Required) The range to which we should apply the condition or criteria one.
- criteria1 = (Required) It is used to determine which cells to add. criteria1 is applied against criteria_range1.
- criteria_range2 = (Optional) The range to which we should apply the condition or criteria two.
- criteria2 = (Optional) It is used to determine which cells to add. criteria2 is applied against criteria_range2.
Note :
- The wildcard characters i.e.? and * can be used in criteria.
- It ignores the text values if any in the provided range.
- Conditions with text or any logical operators must be enclosed in double quotes.
- Use “<>” condition for non-black cells and “” condition for blank cells.
- If the range contains any TRUE or FALSE values in it, then the TRUE is considered as 1 and FALSE as 0.
- Conditions containing TEXT are case insensitive.
3. How to use it with an example?
Imagine you have been given a dataset with few details in it.
If we are required to sum the numbers based on some criteria like DATE and LOCATION.
Let’s see how to do it!!!
To fetch the total sales for the “West” location during the period before “1st March 2019”
We are gonna use the below formula:
**Explained** =SUMIFS(C3:C9,B3:B9,"=EAST",A3:A9,"<01/03/2019")
C3:C9 = range of cells which contain numbers to be added
- B3:B9 = The range to which we should apply the “EAST” condition or criteria
- “=EAST” = It is used to determine which cells to add from the range B3:B9
- A3:A9 = The range to which we should apply the “DATE” condition or criteria
“<01/03/2019” = It is used to determine which cells to add from the range A3:A9
Hope it has helped you…
