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

Pandas counting and summing specific conditions

Are there single functions in pandas to perform the equivalents of SUMIF, which sums over a specific condition and COUNTIF, which counts values of specific conditions from Excel? I know that there are many multiple step functions that can be used…
user3084006
  • 5,344
  • 11
  • 32
  • 41
23
votes
4 answers

Selecting a Specific Column of a Named Range for the SUMIF Function

I am trying to create a SUMIF function that dynamically adds up values in a specific column of a named range in my Excel sheet. It is very easy to do this when there is no named range : The formula picks out all the cells that contain "London" in…
Cla Rosie
  • 345
  • 1
  • 2
  • 10
19
votes
5 answers

Excel SUMIF between dates

I have column A with date values formatted as mm/dd/yyyy. I am trying to sum the values of column B if A >=DATE(2012,1,1) AND =SUM(B:B) sums B properly, but if I try to use =SUMIF(B:B,A:A>=DATE(2012,1,1)) the value returned is 0.00. I'm assuming…
spassen
  • 1,550
  • 8
  • 20
  • 32
17
votes
4 answers

Google Sheets ArrayFormula with Sumifs

Usually don't need help with sheets but I think my brain is imploding from thinking on this too much. Trying to fill an entire column with an array formula that sums values from a separate column based on conditions from two other columns. If that…
Catu
  • 715
  • 1
  • 6
  • 13
14
votes
4 answers

SUMPRODUCT vs SUMIFS

I'm trying to make an in-company guide to Excel (we're a small non-profit and sorely need some sort of baseline guide). However, I've gotten stuck trying to explain the differences between SUMPRODUCT and SUMIFS. My understanding is that SUMPRODUCT…
Kelley Hamrick
  • 197
  • 1
  • 2
  • 13
11
votes
1 answer

SUMIF with FIND as criteria

I'm trying to summarize data from several Google spreadsheet' sheets in a single one but I have some issues with SUMIF and FIND. To Sum data from another sheet I have to use this (at least that's the only solution I've…
maugch
  • 1,276
  • 3
  • 22
  • 46
8
votes
7 answers

Using SUMIFS with multiple AND OR conditions

I would like to create a succinct Excel formula that SUMS a column based on a set of AND conditions, plus a set of OR conditions. My Excel table contains the following data and I used defined names for the columns. Quote_Value (Worksheet!$A:$A)…
Dominic
  • 587
  • 3
  • 9
  • 19
7
votes
2 answers

SUMIF dynamically change summing column

I am using SUMIFS and want the sum_range dynamically to change according to the name I have of a column. I have a table with about 100 columns. Say one of these columns is Paid_BC_items. I want a formula that looks for which column Paid_BC_items is…
James Adams
  • 331
  • 1
  • 5
  • 18
6
votes
2 answers

Google Sheets Error "Array Arguments to SUMIFS are of different size"

Converting from an Excel file (where this works fine), I have a SUMIFS formula that is returning an error "Array Arguments to SUMIFS are of different size". The formula in question looks like…
Scott
  • 3,663
  • 8
  • 33
  • 56
6
votes
4 answers

Excel SUMIFS equivalent in R

I'm very very new to R and am looking at ways of recreating an Excel VBA macro and Excel worksheet functions such as SUMIFS. SUMIFS sums a column if the row has entries matching multiple conditions on its other columns. I have the below data frame…
Barnaby1
  • 71
  • 1
  • 1
  • 3
5
votes
3 answers

SUMIFS (not blank, and othe conditions)

I am trying to use SUMIFS to sum a couple of conditions. I want my sum range to be column A and my first criteria range is column B, the criteria is that column B has something in it or essentially it isn't blank/0. The next criteria is if column C…
ryan
  • 79
  • 1
  • 2
  • 8
5
votes
2 answers

Average a range of values if the date associated with it falls within a date range

I'm trying to average a range of values if its associated date falls between two specified dates. The following function works: AVERAGEIFS($1:$1,$2:$2,">=1/1/2014",$2:$2,"<=1/2/2014") ...in this case the values that you want to average are in row…
Ryan Chase
  • 2,384
  • 4
  • 24
  • 33
5
votes
2 answers

SUMIFS with date and name criteria... month and year only

I am trying to get a SUMIFS formula to check a column of dates and sum only the values that correspond to the matching year and month of the criterion date. I would also like this SUMIFS to include a name criterion along with the date. i.e. Cell A1:…
Zane
  • 53
  • 1
  • 1
  • 5
5
votes
1 answer

Sum data from dates during this year using Google Spreasheets

I have a list of books I've read over the years, along with page counts, stored in Google Spreadsheets. For each book, I keep a pagecount and the date I finished the book. I'd like to sum up the pagecounts of all the books I've read this year. So…
Jenee
  • 53
  • 1
  • 4
5
votes
2 answers

Using Excel sumifs to sum matrix elements

Is it possible using Excel's sumifs to find the sum of elements of a matrix using condition on row and column? For example matrix: c d e a 1 2 3 b 4 5 6 I would like to sum elements of b row, d and e columns. The solution should…
danas.zuokas
  • 4,551
  • 4
  • 29
  • 39
1
2 3
…
82 83