# EXAMPLE - SUMIF Function

This example can be used to sum the values in a column based on a condition and organized by group.

Function:

Item

Description

SUMIF Function

Generates the sum of rows in each group that meet a specific condition.

WEEKDAY Function

Derives the numeric value for the day of the week (1, 2, etc.). Input must be a reference to a column containing Datetime values.

Source:

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

EmployeeId

Date

Sales

S001

1/23/17

25

S002

1/23/17

40

S003

1/23/17

48

S001

1/24/17

81

S002

1/24/17

11

S003

1/24/17

25

S001

1/25/17

9

S002

1/25/17

40

S003

1/25/17

S001

1/26/17

77

S002

1/26/17

83

S003

1/26/17

S001

1/27/17

17

S002

1/27/17

71

S003

1/27/17

29

S001

1/28/17

S002

1/28/17

S003

1/28/17

14

S001

1/29/17

2

S002

1/29/17

7

S003

1/29/17

99

Transformation:

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:

 Transformation Name New formula Single row formula WEEKDAY(Date) 'DayOfWeek'

First you wish to examine weekday sales, when DayOfWeek < 6. For each day of the week, you can preview the following aggregation:

 Transformation Name Pivot columns groupId sumif(Sales, DayOfWeek < 6)

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

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

 Transformation Name Pivot columns groupId sumif(Sales, (DayOfWeek >= 5))

Results:

The following are the results for the weekend:

EmployeeId

sumif_Sales

S001

42

S002

126

S003

142