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
2
votes
1 answer

sum SQL in google sheet query function doesn't add

I have been working on a sheet for a while and I would like to use the query function of google sheet for adding the amount of the ingredients of different meals, to create an 'overall' shopping list, but it seems to be that the numbers are not…
user3290356
  • 95
  • 1
  • 10
2
votes
1 answer

Using SUMIF/SUMIFS for a Column with both Numbers and Strings

I'm making somewhat of a bill tracker of my own in Google Sheets. This is snippet so far what I have. Google Sheet Example Image What I want to accomplish is on cell K2, you can see that if the cell to the right of the K column is not checked (aka…
Dan A.P
  • 23
  • 3
2
votes
1 answer

Array argument issues for Sumif

I'm trying to make the following formula work and it's giving me "Array arguments to SUMIFS are of different size." =SUMIFS(ChronologicalExpenditures!I2:I995, ChronologicalExpenditures!N3:N, "*"&H2&"*", ChronologicalExpenditures!B3:B, "Expenditure,…
Emil
  • 59
  • 6
2
votes
3 answers

Using ARRAYFORMULA to Calculate Running Total of Payables (Alternative to INDIRECT)

I use a Google Spreadsheet to keep track of the accounts payable per vendor. There is a sheet per vendor in the Spreadsheet. A simplified sheet looks like this: When I receive a new invoice, an entry for the amount is made in the Credit column and…
Code Poet
  • 11,227
  • 19
  • 64
  • 97
2
votes
2 answers

Excel Sumifs using numbers stored as text in criteria

I am trying to use the SUMIFS() formula in excel to exclude certain rows from a table, but the criteria range includes numbers stored as text. In the picture below I want to exclude the rows where entity id is "101000". The SUMIFS() formulas I have…
Chris Kiniry
  • 499
  • 3
  • 13
2
votes
2 answers

Sum Across Multiple Tabs, Only If Tab is Flagged as being Active

I have 5 tabs: "Start" "2020" "2019" "2018" "End" I would like to sum the same cell (eg. cell A2) across all tabs, ONLY if the tab is flagged as being Active (eg. cell A1 of each tab will have 1 or 0 depending on if it's active). Normally, I would…
user18729347
  • 43
  • 3
2
votes
1 answer

How do I use an Array as a range?

Might be a dumb question to begin with, but I need to store data within an array, and then use that data in a function that calls for a range. I have the following code for my array - Function UnitCheckArr() Dim UnitValueArr(2 To 250) As Long Dim…
Drawleeh
  • 297
  • 1
  • 10
2
votes
1 answer

Can one do a sumif(s) on a dynamic (spilled) range and return a (2d) array?

Just curious if it's possible to get spill range output from a sum or sumif formula in excel. Given a 2D array, I'd like to sum some rows but preserve the columns. My concern is that once you add a sum or sumif to a spilling formula (sequence,…
JPC
  • 25
  • 3
2
votes
1 answer

How to sum the total in google spreadsheets based on a true/false column

I am trying to make a list of sold items with this formula used to check it off: =CONCATENATE(COUNTIF($A$3:$A$50,TRUE), "/", COUNTA($C$3:$C$50), " Items sold ") I want each sold item to add up the prices in column B and put the total profit…
Timothy Keyes
  • 23
  • 3
2
votes
2 answers

SUMIFS with OR criteria in Google Sheets

I want to add values in Column C with the conditions that Column B = 0, AND Column A = "a" OR "b" From what I've searched, this would work, but only in excel: =SUM(SUMIFS(C:C,B:B,0,A:A,{"a","b"})) I've tried adding "ArrayFormula" as this is a…
Laine
  • 35
  • 1
  • 5
2
votes
3 answers

Nesting SUMIFS in SUBTOTAL

I have a table like this: Activity Month Budget Planned EUR Activity 1 January € 1,000.00 Activity 1 January € 2,000.00 Activity 1 February € 2,000.00 Activity 2 January € 1,200.00 Activity 3 …
Christina K
  • 33
  • 1
  • 4
2
votes
2 answers

How to include two2 sum ranges in SUMIFS (excel)

Hi everyone, What I want to achieve in this task is: If AAPL appeared the most in the winning category, then the Best performance will be AAPL regardless of whether the total P/L (total P/L for winning & lossing) for AAPL is higher or lower than…
weizer
  • 1,009
  • 3
  • 16
  • 39
2
votes
3 answers

sumifs and countifs equivalent in R

guys Say I have a dataset that looks like this: id value year total_value total_frequency 1 10 2019 44 4 1 15 2019 44 4 1 12 2020 44 4 1 7 2020 44 …
PanemForever
  • 23
  • 3
2
votes
2 answers

Adding or subtracting values based on a vector of check boxes in Google Sheets

I hope you can help me with this: I'm trying to create a savings-control sheet where I list my monthly payment and I'm trying to use the SUMIF formula to subtract my expenses by selecting what I have currently payed but I don't know if this may work…
SecaidaDevStudent
  • 95
  • 1
  • 10
2
votes
1 answer

Excel - sum base on row and multiple columns

How to get sum of column labels 15 and 8 of a particular date Input: Output: need for all rows One way is do sum along horizontal yet not sure to match the dates... I have >50K columns in real dataset. sumif along rows link…
Mutum Pamel
  • 41
  • 7
1 2 3
…
82 83