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
4
votes
2 answers

Excel: Optimizing a bunch of very heavy SUMIFS

I have some data that I want summed matching certain criteria Example data: A B C D Id Id2 Id3 Val 1 1 8 6 1 2 7 7 1 3 3 8 1 4 6 4 1 4 78 7 1 1 2 9 1 3 1 4 1 4 3 6 …
abcde123483
  • 3,885
  • 4
  • 41
  • 41
4
votes
1 answer

SumIfs formula with more than one "multiple criteria"

I am trying to write a formula as such: =SUM(SUMIFS([SUM TOTAL], [Range 1], {[Criteria 1.1], [Criteria 1.2]}, [Range 2], [Criteria 2], [Range 3], [Criteria 3], [Range 4], {Criteria 4.1], [Criteria 4.2]})) Leaving Range 4 with only 4.1 along with…
Drew D
  • 127
  • 6
4
votes
1 answer

R - If date falls within range, then sum

I have managed to do this in excel easily but want to complete this in R: I have two data frames: MediaPlanDF (215 obs, 29 var) I am only concerned with 4 of the variables: Start Date (flight date), End Date (flight date), Daily Spend, Daily…
YungBoy
  • 235
  • 4
  • 14
4
votes
4 answers

Cumulative mean with conditionals

New to R. Small rep of my df: PTS_TeamHome <- c(101,87,94,110,95) PTS_TeamAway <- c(95,89,105,111,121) TeamHome <- c("LAL", "HOU", "SAS", "MIA", "LAL") TeamAway <- c("IND", "LAL", "LAL", "HOU", "NOP") df <- data.frame(cbind(TeamHome,…
Sburg13
  • 121
  • 5
4
votes
1 answer

Cognos equivalent of excel's sumif() function

I'm familiar with Excel and SQL, but new to Cognos. I'm doing a conditional sum on [Total Margin] for each [Item Code]. This result should show on each row for the each item. I've tried 2 approaches in Cognos and a proof of concept in Excel. See…
Fractional
  • 153
  • 1
  • 2
  • 10
4
votes
2 answers

Why curly braces ({ }) don't work in a SUMIFS in Excel?

I'm trying to do this in Excel 2010: =SUMIFS(Main!I:I,Main!J:J,"A",Main!K:K,{"OptionA","OptionB"}) I want to sum up all Main!I:I where this is true: Main!J:J is "A" Main!K:K is either "OptionA" or "OptionB" I've seen the curly braces in other…
pepepapa82
  • 167
  • 2
  • 10
4
votes
2 answers

SUMIF used together with FILTER function in Google Spreadsheet

It seems that Google Docs doesn't offer the SUMIFS function. Instead, it was suggested to use a workaround combining SUM with FILTER I'm trying to sum values filtering months in a specific year. I've created a formula like this:…
walter tabax
  • 41
  • 1
  • 1
  • 3
3
votes
2 answers

How to use the SUMIFS function in an ARRAYFORMULA, matching two criteria, to avoid copying down?

Update: This is a duplicate question - see : Google Sheets ArrayFormula with Sumifs I am raising this question because someone asked it today in a poorly structured question, and theirs was blocked for answering. A similar question was answered…
kirkg13
  • 2,955
  • 1
  • 8
  • 12
3
votes
2 answers

Explain ArrayFormula expansion rules

I'm trying to understand ArrayFormula function, but the official documentation is very poor. For example, I've got a google sheet with amounts and subtotals calculated with ArrayFormula. There are two formulas, and both produces some magic. The…
raacer
  • 5,302
  • 3
  • 27
  • 46
3
votes
2 answers

Google Sheets Sumifs with date condition

I'm getting an error when trying to grab the money I have spent for a month on another Google Sheet using SUMIFS formula. I'm new to writing formulas and this is the first bottleneck I haven't been able to find. I'm trying to write a formula in my…
ottmduncan
  • 41
  • 1
  • 2
3
votes
3 answers

SumIF Using Table/Named Range Instead of Single Cell Criteria

I have 2 sheets in a workbook (Sheet1, Sheet2). Sheet 2 contains a table (Named Table1) with 5 columns: Takeaways Household Clothing Fuel Groceries On sheet one, I have 2 columns: Expense Name Expense Total Now, what I am trying to do is: Set…
Eitel Dagnin
  • 959
  • 4
  • 24
  • 61
3
votes
1 answer

Adding a Sumif formula to cell error using c#

I'm trying to add a formula to a cell in multiple rows in Excel through C#. What i'm trying to achieve with the formula is to SUM all the cells which does not contain the string N/A, in column D to S in every row. This is the formula i'm trying to…
Brewsli
  • 127
  • 1
  • 10
3
votes
2 answers

SUMIFS in an ARRAYFORMULA not working

I'm creating a sales tracker and struggling with a SUMIFS ARRAYFORMULA. This is the formula:- =arrayformula(SUMIFS('LIVE!'!$C$4:$C,'LIVE!'!$K$4:$K,$C$14:$C,'LIVE!'!$J$4:$J,"Ex-Yard")) This is a snapshot of the columns I'm looking at: Sheet 1…
Guy Griffiths
  • 31
  • 1
  • 2
3
votes
2 answers

SumIF three conditions meet return the forth column value

I am having two sheets Sheet1 & Sheet2, Sheet1 having four columns like EMP No, Project Number, Month & Working Days. In the sheet2 I have same four column but some employee will be worked in two projects for that month, For example: If Emp No,…
Gyana Prakash
  • 79
  • 2
  • 3
  • 13
3
votes
2 answers

update formula row and column while dragging

I have listed out data row-wise and I wish to consolidate the data column-wise as shown. In cell G4, I used the formula SUMIFS($C$4:$C$13,$A$4:$A$13,F3,$B$4:$B$13,E4) I have to update the formula manually (row and column index lookup) each time…
Melko
  • 41
  • 7
1
2
3
…
82 83