SUMIF in 5 languages (VBA/SQL/PYTHON/M query/DAX powerBI)

How to make your favorite Excel feature in another analytic language (VBA/SQL/PYTHON/M query/DAX powerBI)

photo by Clayton Robbins on Unsplash

Excel is a powerful spreadsheet used by most people working in data analysis. The increase of volume of data and development user-friendly tools is an opportunity of improvement of Excel reports by mixing them with another tool or language.

As working for a financial reporting department I faced the need to boost our reporting tools. A simple way to start working with a new language is to translate what we use to do in excel, in another language. “How can I pivot this?”, “How can I vlookup that ?”.

In this article I will share with you how you can make VLOOKUP in 5 different languages: VBA, python, SQL, DAX (Power BI), M (Power query). it will simple tips but if you want to get more detailed article don’t forget to follow me!

SUMIF in Excel

The SUMIF function in Excel returns the sum of cells that meet a single condition. In the condition you can use logical operator (<,>,<>,=) and wildcards (*,$) for partial conditions.

=SUMIF (range, criteria, [sum_range])

Range = range of cells where you want to check the criteria.

Criteria= the condition.

Sum_Range= optional argument to be used if the cells that have to be sum are different from “range” in argument 1.

Image by Author

We will use a simple example with a tab with items and their relative revenue. In cell F2 we can put a SUMIF formula with arguments : Range=A2:A8, Criteria=cell(E2),Sum_Range=B2:B8.

=SUMIF(A1:A8,E2,B1:B8)

SUMIF in VBA

Visual Basic for Application (VBA) is an implementation of Microsoft Visual Basic integrated into Microsoft Office applications.

On your code, you can set up a variable to store the result of the SUMIF result using the statement “WorksheetFunction.sumif”. It works exactly as the function itself. Then you can store the result directly into cell F2.

Range("F2") = WorksheetFunction.SUMIF(Range("A2:A8"), Range("E2"), Range("B2:B8"))

Let’s do it properly by declaring variables now:

Sub sumif()
Dim wb As Workbook
Dim wsD As Worksheet
Dim Arg1 As Range
Dim Arg2 As Range
Dim Arg3 As Range
Set wb = ThisWorkbook
Set wsD = wb.Worksheets("Sheet1")
Set Arg1 = wsD.Range("A2:A8") 'where to find
    Set Arg2 = wsD.Range("E2") 'what to find
    Set Arg3 = wsD.Range("B2:B8") 'what to sum
wsD.Range("F2").Value = Application.WorksheetFunction.SUMIF(Arg1, Arg2, Arg3)
End Sub

SUMIF in SQL

SQL ( Structured Query Language) or sequel, is a standard language for storing, manipulating and retrieving data in databases. It is one of the common upgrade done by companies that face limits with Excel. Usually, the first reaction is to negotiate some budget in order to store the data into a database and use SQL to “speak” with this database and organise, manipulate the data. The language is also highly appreciable. Close to a natural language, you don’t feel coding when typing simple SQL request.

Let’s start with creating a table with items and revenue:

CREATE TABLE table1 (
    Item varchar(255),
    Revenue int    
);
INSERT INTO table1
VALUES ('Item1', 62);
INSERT INTO table1
VALUES ('Item2', 53);
INSERT INTO table1
VALUES ('Item1', 84);
INSERT INTO table1
VALUES ('Item3', 7);
INSERT INTO table1
VALUES ('Item1', 11);
INSERT INTO table1
VALUES ('Item4', 6);
INSERT INTO table1
VALUES ('Item4', 12);
SELECT*FROM TABLE1

Image by Author

Now we can use a simple CASE statement:

SELECT SUM(CASE WHEN Item = 'Item1' THEN Revenue END) FROM table1;

Image by Author

We select the sum of the case where the column Item equal ‘Item1’ by returning what is after THEN, the Revenue.

SUMIF in Python

Python is an interpreted, high level language with a generic purpose. It is used in a large range of application, including data analysis. We can present python by saying “For all application its libraries”. And for data, without surprise, we will use the famous Pandas.

First, we create a data frame get our items and revenue:

import pandas as pd
items = {'Item': ['Item1','Item2','Item1','Item3','Item1','Item4','Item4'],'Price': [62, 53,84,7,11,6,12]}

Then with Pandas we select the rows of the dataframe where the columns Item data is ‘Item1’, and we sum them.

items.loc[table1["Item"] == "Item1", "Revenue"].sum()

VLOOKUP in M

M is the powerful language behind the tool power query. Even if you are working on the query editor, every single step will be written in M. M stands for Data Mashup or Data Modeling. I highly recommend to have a look at this language, we can do so much more than just using the graphical interface.

Regarding the SUMIF, the graphical interface is so smooth that we can directly use it. You just have to import your table into the graphical interface and then use the filter in the column Item:

Image by Author

Then you select the column “Revenue”, and on the field Transform do the SUM:

Image by Author

And you get the sum. Another way is to use the Group By in button in the left, but in the scale of our example, it will add more step that we can skip by directly filter and sum. I will present this with another feature where it will be more useful (The pivot table).

In DAX

DAX stands for Data Analysis Expressions. More than a language, it is a library of functions and operators that can be used to build formulas in Power BI and Power Pivot.

The SUMIF if a good opportunity to introduce the function CALCULATE. It is one of the most famous function in Power BI that evaluates an expression after applying a filtering rule. It is more close to the SUMIFS as you can apply several filters arguments.

CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

Expression: is the expression to be evaluated.

Filter1,2,…: can be a Boolean expression, a table filter or even another function.

In our case, the expression to be evaluated is the sum of Table1 revenue, by applying one filter table in column “Items”=”Item1"

sumif = CALCULATE(SUM(Table1[Revenue]),Table1[Items] = 'Item1')

CONCLUSION

This article is a part of a set of articles where I share my way of doing an Excel feature in other languages. For sure, when you decide to move from Excel to programming, your approach of data analysis will totally change and you will learn that there are tons of way to get to your solution. If you have another manner to do this or even know how to do it with other languages, feel free to share them in the comments or by contacting me in my social networks! I will be pleased to read your thoughts!