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.

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)

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.

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)))

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:-

• Sum Multiple Columns Dynamically Across Rows in Google Sheets.
• How to Sum Each Row in Google Sheets.
• Sum of Matrix Rows or Columns Using Sumif in Google Sheets.