Stack Overflow
Stack Overflow

Questions tagged [sumifs]

SUMIFS is an Excel function, introduced with Excel 2007 (v12.0), which allows for the summation of values selected according to several criteria – addition is only of cells meeting all the conditions.

Microsoft describes the syntax so:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,criteria2], …)

AbleBits compares SUMIFS and SUMIF and gives examples of usage. Between the two the position of sum_range differs and for SUMIFS each criteria_range must contain the same size (number of rows and columns) as the sum_range argument.

1239 questions
3
votes
5 answers

How use SUMIF with month & year with text criteria on external sheet

How use SUMIF with month & year with text criteria, Exp! I want to sum A B C Date Item Code QTY 01-12-16 86000 50 15-12-16 86021 20 01-02-17 …
Mohammad Barzakh
  • 39
  • 1
  • 1
  • 10
3
votes
1 answer

Sumifs in Excel-VBA

I have some problem with sumifs in vba: Dim Arg1 As Range 'the range i want to sum Dim Arg2 As Range 'criteria range Dim Arg3 As Variant 'the criteria Set Arg1 = ThisWB.Sheets("Sheet1").Range("B2:B100") Set Arg2 =…
CsCs
  • 43
  • 1
  • 1
  • 7
3
votes
2 answers

COUNTIF/SUMIF gives error if criteria string is longer than 256 characters

While trying to use COUNTIF and SUMIF with a table that regularly has long comments, I kept getting a #VALUE error. A little bit of research said that the error could be due to the criteria string topping the 256 character point. Any suggestions on…
TesseractE
  • 415
  • 4
  • 13
3
votes
2 answers

Google Sheets: SumIF(s) Summing by Week Day

I have a table with dates in Col A and values in Col C. I want to sum those values by week daylike this: sumifs(C$2:C;A$2:A;weekday(A$2:A)=1), but even if I substitute 1 for any other value, the sum stays 0. There are only positive, non-zero…
Ralf H
  • 1,392
  • 1
  • 9
  • 17
3
votes
2 answers

Create a vector in R by summing rows based on multiple criteria

I have financial data which is currently in 15 minute intervals, but I want to convert the intervals from 15 minutes to 30 minutes before I conduct the rest of my analysis. As such, I would like to sum the traded volumes for two adjacent 15 minute…
user5800169
  • 31
  • 3
3
votes
1 answer

SUMIF function with criteria in another cell

Say I have to sum up the cells in column B if their corresponding cells in column A <= the value in some specific cell in column C. Instead of SUMIF(A1:A10,"<=10",B1:B10), I tried SUMIF(A1:A10,"<=C1",B1:B10) and it didn't work. How do you fix it so…
forestcat
  • 33
  • 1
  • 3
3
votes
1 answer

sum if greater than in r

I have a dataframe (obs) with 145 rows and more than 1000 columns plus a numeric vector with 145 values (thr). I would like to derive another vector (sumifs) with 145 elements where each element is the sum of the values of obs[n,] >= thr[n]. I…
Corrado
  • 157
  • 4
  • 9
2
votes
2 answers

calculating values in excel using sumproduct

I've tried sumproduct and i've tried sumif but I think what I need is a combination (or a better understanding of sumproduct) Here is my data state | percent NSW | 0 NSW | 20 VIC | 0 SA | 0 WA | 15 NSW | 0 NSW | 70 What I want to try and calculate…
php-b-grader
  • 3,191
  • 11
  • 42
  • 53
2
votes
1 answer

Concatenate conditions with array formula and sumif in google sheets

I'm trying to make a sumifs with an array formula using sumif like this =arrayformula(if(len(B2:B)=0;;sumif(DATOS!B:B&DATOS!S:S&DATOS!A:A;B3:B&T2&11;DATOS!G:G))) The formula works fine except when the condition uses another kind of operators like…
Zyloric
  • 23
  • 4
2
votes
6 answers

How to sum on multiple criteria using multiple data frames

I have this df where I pull the criteria from: id Criteria 1 Criteria 2 1 3/1/2022 Black 2 5/2/2022 Black 3 3/1/2022 Blue I want to use the criteria to sum amounts from the following…
rushi
  • 225
  • 1
  • 3
  • 7
2
votes
1 answer

Multiple conditions using SumIFs

I am trying to automate sumIfs using multiple criteria for the below sample table. Column A to F is the data and the output table is displayed in column H through K. The output table sums the values in the data based on condition1 and condition2. I…
Sree
  • 21
  • 2
2
votes
1 answer

How to create a statement to count total of date and time

I have data in two separate columns like this But how can I count how many rows are occurring within say a window of 6 AM to 10 PM and outside a window of 10:01 PM to 5:59 AM? The first column cells are formatted as General. The second column cells…
Rackman
  • 25
  • 3
2
votes
1 answer

Google Sheet - calculating attendance from moving date range

Good day folks, we have a Google Sheet with training attendance. We are meeting on weekly basis and calculate attendance from the past four months, last 36 month and historical total. Situation: Currently, we calculate the attendance by assigning 2…
Majk Šenkeřík
  • 23
  • 4
2
votes
2 answers

How to sum with index match formula in MS excel

I would like to sum the data in column F (Sheet1) and show the result in column B (Sheet1)(From B4 to B9). But the sum range should be created using vlookup or index/match. The column E in sheet1 matched with Column B in sheet2 and take the column A…
Deepak
  • 473
  • 1
  • 10
  • 32
2
votes
1 answer

Google Sheets SumIfs with left formula

I want to use the sumifs formula, but the sum interval range has text in it. Example: |Criteria|Sum Interval| |--------|------------| | A | 1 - Good | | A | 2 - Regular| | C | 3 - Bad | So, I want to check the criteria field…
marceloguaycurus
  • 82
  • 7
1 2
3
…
82 83