HomeGoogle DocsSpreadsheetHow to Use the Sumif Function Horizontally in Google Sheets

How to Use the Sumif Function Horizontally in Google Sheets

Published on

We can use the function SUMIF horizontally in Google Sheets but not in an array formula.

Wait! I have an array formula Sum If too that using DSUM. We will discuss that in a later part of this tutorial.

Sumif horizontally means take the SUMIF criteria from the header row, not from columns, as we usually do.

In such a type of conditional sum, mostly, we may come across two different sorts of problems.

For example, see this dataset.

ItemJanFebMarAprMayJunJulAugSepOctNovDecTotal
A5837515
B163521
C254111116
D10112111

Assume the above table is starting from row # 2. I mean, the field labels “Item,” “Jan,” “Feb,” etc., are in row # 2.

In row # 1, we will mark some cells with “OK.” For example, the cells above, “Jan,” “Apr,” “Jul,” and “Oct.” We want to total only such columns row-wise.

Sometimes, we may not want to specify the columns separately as above. Then? Instead, we will use the field labels.

To understand it clearly, copy the above table in a blank sheet in your spreadsheet and follow the below steps/points.

SUMIF Horizontally in Google Sheets Based on Marked Columns

As I have already mentioned, we can use the SUMIF function horizontally in Google Sheets in a non-array form. We will use alternatives for array use.

If you are new to SUMIF, please read my post – Simplified the Use of SUMIF function in Google Sheets.

The below formula in O3 is copied down.

=sumif($C$1:$N$1,"OK",C3:N3)
Formula to Sumif Horizontally in Google Sheets

Syntax: SUMIF(range, criterion, [sum_range])

The ‘range’ as per the formula is the row reference $C$1:$N$1. The ‘criterion’ is the string “OK” in that row, and the ‘sum_range’ is C3:N3.

Only the ‘sum_range’ is relatively referenced (no dollar sign used with C3:N3). So when I drag the formula down, the sum_range will adjust the row numbers accordingly.

Array Formula Alternative (DSUM Formula)

We can’t use the above SUMIF horizontal formula as an array formula to automatically expand down from O3. Here is my DSUM alternative to SUMIF horizontally in Google Sheets.

Learn DSUM – How to Use DSUM in Google Sheets – Explained With Pictures.

Empty the formulas in O3:O6 and insert my below DSUM in O3.

=ArrayFormula(IFERROR(transpose(dsum(transpose(B1:N),transpose(B3:B),{if(,,);"OK"}))))

It will expand from O3 to all the rows down. The formula will insert the total based on the marked columns in all rows that have values.

If you want to learn this DSUM use, please follow the formula explanation part below or skip it.

Formula Explanation

The data range is B1:N6. For the explanation purpose, I will use this range instead of the open range B1:N in DSUM.

We may not be able to test the formula in an open range as TRANSPOSE is involved.

Let’s learn how the above DSUM formula works as an alternative to SUMIF horizontally in Google Sheets.

Syntax: DSUM(database, field, criteria)

database: transpose(B1:N6)

field: transpose(B3:B6)

criteria: {if(,,);"OK"}

Let’s transpose B1:N6 (the database) B3:B6 (field) and try the criteria in the independent range and see that output.

Sumif Horizontally Using DSUM Array Formula

If you insert the above formulas in B9, J9, and P9, you will get the output as shown in the image.

Note:- Before attempting the transposed database, transposed field, and the criteria, you should remove the existing DSUM formula to SUMIF horizontally from cell O3.

Now try the below DSUM ArrayFormula in O3.

=ArrayFormula(dsum(B9:G21,J9:M9,P9:P10))

You will get the same result as per earlier O3:O6, but horizontally. Just transpose it.

=ArrayFormula(transpose(dsum(B9:G21,J9:M9,P9:P10)))

From the above explanation and your testing, you can understand the DSUM formula used in cell O3.

SUMIF Horizontally in Google Sheets Based on Header Row

Here we are going to use the header row itself. Please see the below screenshot.

Insert the below non-array formula in cell O2 and copy it down.

=sum(ArrayFormula(sumif($C$1:$N$1,{"Jan","Apr","Jul","Oct"},C2:N2)))
Header Row as Criterion in DSUM

It is a non-array formula. Other than SUMIF, this formula takes the help of the functions SUM and ArrayFormula.

What is the reason for the additional functions’ use?

Because, in the first example, there was only one criterion, and that is the string “OK.” But, here, there are four criteria, and they are “Jan,” “Apr,” “Jul,” and “Oct.”

So the ArrayFormula is required. The output will be in four cells. The SUM function totals them.

Can We Use DSUM Array Formula in this Case?

Here also we can depend on the DSUM magic.

There are not many changes compared to our first example.

Here is the formula, and see yourself what the changes are.

=ArrayFormula(IFERROR(transpose(dsum(transpose(B1:N),transpose(B2:B),{B1;"Jan";"Apr";"Jul";"Oct"}))))

I am not explaining it as you can read it yourself by taking out the database, range, and criteria and test it on your sheet.

Especially pay your attention to the criteria.

Further, I am leaving my example sheet below for you to experience the formulas.

For your info, you can use MMULT to write array formulas to solve the above problems.

Thanks for the stay. Enjoy!

Sample_Sheet_24421

Related:-

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here