EXAMPLE - SUMIF Function

The SUMIF function can be used to sum the values in a column based on a condition and organized by group. See SUMIF Function.

Source:

The following data identifies sales figures by salespeople for a week:

EmployeeIdDateSales
S0011/23/1725
S0021/23/1740
S0031/23/1748
S0011/24/1781
S0021/24/1711
S0031/24/1725
S0011/25/179
S0021/25/1740
S0031/25/17
S0011/26/1777
S0021/26/1783
S0031/26/17
S0011/27/1717
S0021/27/1771
S0031/27/1729
S0011/28/17
S0021/28/17
S0031/28/1714
S0011/29/172
S0021/29/177
S0031/29/1799

Transform:


You want to know how your salespeople are doing by the day of the week. To the above, you add a column that identifies the day of the week:

derive type:single value:WEEKDAY(Date) as:'DayOfWeek'

First you wish to examine weekday sales, when DayOfWeek < 6.

For each day of the week, you can preview the following aggregation:

pivot value:SUMIF(Sales, (DayOfWeek < 6)) group:EmployeeId

Performance is listed in the following order: S001, S002, S003

To analyze the weekend, you change the above to the following:

pivot value:SUMIF(Sales, (DayOfWeek >= 5)) group:EmployeeId

Results:

The following are the results for the weekend:

EmployeeIdsumif_Sales
S00142
S002126
S003142
Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation