How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Sunday

How to Use the Sumif Function Horizontally in Google Sheets

Posted by   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 types 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 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 on 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)))

I hope, from the above explanation and your testing based on it, you could understand the DSUM formula in O3.


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

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!