This function was a life saver for me recently!
I have some data with 30 columns and nearly 2000 rows which needs to be regularly summed up based on a host of criteria. Add to that I need to run several slicing and dicing operations to derive different conclusions.
Now doing this with a simple SUMIF or PIVOT is difficult enough and considering the time crunch, its a nightmare!!
What I would do earlier is to get a sum of the criteria individually and then add them up together. Consider this example of some Sales data where we would need some basic data analysis.
And where reports can be created as:
Now doing this with Pivot tables would mean that I would probably have to consolidate it in different pivots and then get the data in a third table for deriving the region wise sales where CSAT is above a certain value. And changing these pivots to get a new set of conclusions would have been extremely time consuming.
Now let's look at how to use the function ->
Function Description:
SUMIFS ( Sum Range, Criteria Range1, Criteria1, Criteria Range2, Criteria2, ...., Criteria RangeN, CriteriaN)
Sum Range: C3:C8 - Range of values to be summed
Criteria Range: B3:B8 - Range of values where region is stored
Criteria - "US" for summing up only US related sales values
You can quickly add any more criteria like previous CSAT > 3.5 but current CSAT > 4 etc...
Note: Please note that you will need to ensure data sanctity before using these functions as it will only pick values with "US" and something with "USA" would be missed out
Tweet
I have some data with 30 columns and nearly 2000 rows which needs to be regularly summed up based on a host of criteria. Add to that I need to run several slicing and dicing operations to derive different conclusions.
Now doing this with a simple SUMIF or PIVOT is difficult enough and considering the time crunch, its a nightmare!!
What I would do earlier is to get a sum of the criteria individually and then add them up together. Consider this example of some Sales data where we would need some basic data analysis.
- Region wise PLANNED sales where previous CSAT was below 3.5
- Region wise ACTUAL sales where previous CSAT was below 3.5
- Similarly summation with current CSAT
And where reports can be created as:
Now doing this with Pivot tables would mean that I would probably have to consolidate it in different pivots and then get the data in a third table for deriving the region wise sales where CSAT is above a certain value. And changing these pivots to get a new set of conclusions would have been extremely time consuming.
Now let's look at how to use the function ->
Function Description:
SUMIFS ( Sum Range, Criteria Range1, Criteria1, Criteria Range2, Criteria2, ...., Criteria RangeN, CriteriaN)
Sum Range: C3:C8 - Range of values to be summed
Criteria Range: B3:B8 - Range of values where region is stored
Criteria - "US" for summing up only US related sales values
You can quickly add any more criteria like previous CSAT > 3.5 but current CSAT > 4 etc...
Note: Please note that you will need to ensure data sanctity before using these functions as it will only pick values with "US" and something with "USA" would be missed out
Tweet
