How to Include Same Field Twice in SUMIFS Function in Google Sheets

0
135
Google Sheets SUMIFS in office

One of the problem you may face while using functions like DSUM, SUMIFS, SUMPRODUCT or QUERY are the use of criterion. When it comes to include same field twice in SUMIFS the things may not work as you wish.

Whether it’s SUMIFS or DSUM you can get the same result by using the criterion in a proper way. Even SUMPRODUCT can replace the other two functions in Google Sheets much better.

In an earlier Google Sheet tutorial related to DSUM, I detailed how can we include two dates in same field as criteria. It’s simple as you should only know the date formatting techniques and further in DSUM we enter the criteria under field titles. So the formula looks simple.

But things are not that much easy when you want to include same field twice in SUMIFS in Google Sheets. There are two ways you can achieve it. One by possibly using the ARRAYFORMULA together with SUMIFS and the other one without using array. This tutorial follows the latter as it may be easy for you.

Google Sheets SUMIFS in office use

Steps to Include Same Field Twice in SUMIFS Function

How to Include Date Field Twice in SUMIFS?

Suppose your date range is C7:C14 or C7:C, you can include date field twice in SUMIFS as range and criterion. The range, as usual, directly select from the data twice and the criterion you can either call from two cells or directly enter inside the formula. Below is the part of the formula where I used date field twice within SUMIFS.

=sumifs(D7:D14,A7:A14,“Myron Ambriz”,B7:B14,“North”,C7:C14,“>=”&date(2017,7,1),C7:C14,“<=”&date(2017,7,31))

The above SUMIFS formula with multiple date criterion is to extract sum based on items that falls between 01/07/2017 and 31/07/2017.

How to Include Text Field Twice in SUMIFS?

Here the above step may not work unless we use Array Formula. But we can over come that with multiple SUMIFS function.

Multiple SUMIFS in Single Formula

In the above formula we checked range B7:B14 for “North”. But if you want to include “South” also, you need to use another SUMIFS formula. At the end of the above first formula put a “+” symbol and copy paste again the same formula. Only make the change to “North” as “South”. Now the entire formula will look like as below.

=sumifs(D7:D14,A7:A14,”Myron Ambriz”,B7:B14,”North”,C7:C14,”>=”&date(2017,7,1),C7:C14,”<=”&date(2017,7,31))+
SUMIFS(D7:D14,A7:A14,”Myron Ambriz”,B7:B14,”South”,C7:C14,”>=”&date(2017,7,1),C7:C14,”<=”&date(2017,7,31))

Example to How to Include Same Field Twice in SUMIFS Function

See the below sample data.

Sample Data to Include Same Field Twice in SUMIFS Function in Google Sheet

You can use the above formula here. It will sum “Sales Value” if the following conditions meet.

Name of Sales Person: Myron Ambriz

Area: Both North and South

Sales Date: Between 01/07/2017 and 31/07/2017.

Hope you understood now how to use same field twice in Google Sheets SUMIFS. Hope to see you again with another spreadsheet tutorial.

LEAVE A REPLY

Please enter your comment!
Please enter your name here