44632

How to use SUMIFS function for summing values if criteria range exists in a range?

Question:

My data:

A B C a 3 d b 1 a c 8 e d 5

I want to use SUMIFS function, sum range is B1:B4, if the corresponding value in column A exists in column C, sum the values in column B, in this case the sum will be 3 + 5 = 8 I tried =SUMIFS(B1:B4,A1:A4, COUNTIF($C$1:$C$3,A1) but I get zero.

Answer1:

=SUMPRODUCT(SUMIF(A1:A4,C1:C3,B1:B4))

Link for further reading: <a href="https://web.archive.org/web/20141014072615/https://excelxor.com/2014/09/02/countifs-multiple-or-criteria-for-one-or-two-criteria_ranges/" rel="nofollow">COUNTIFS: Multiple “OR” Criteria For One Or Two Criteria_Ranges</a>

Answer2:

What you want is this:

=SUMIFS(B1:B4,A1:A4,A1)

Edit:

I know you asked for a sumif, but that doesn't want to work, entering the below as an array formula (CTRL+SHIFT+ENTER) does the trick.

=SUMPRODUCT(B1:B4,(IFERROR(MATCH(A1:A4,C1:C4,0),0)>0)*1)

Recommend

  • Excel Q - SUMIFS with a 2 dimensional array
  • Use a excel cell reference to indicate tab name inside a formula?
  • Finding SUM except some columns
  • How do I make this formula support multiple rows/dragging?
  • VLOOKUP with two criteria?
  • SUMIF, how can I sum values based only on certain letters/numbers from cell?
  • Count if for excel VBA, and printing results in another range
  • VBA Application Defined Error
  • Extracting the two smallest values corresponding to the unique-to-the-column ID
  • Lookup a date, based on a variable, and then add a number of days to that date, to give a new date
  • Excel VBA-Duplicates run with button/add location
  • Excel sumif with criteria
  • How to get message from MessageIDTerm for Yahoo IMAP profile?
  • Password strong - not require each condition
  • What's the best way to create a menu in jQueryMobile
  • How to calculate total Fridays between two dates in excel on weekly, biweekly and monthly basis?
  • JOOQ nested condition
  • Excel distinct count with conditions
  • Inversing an interpolation of rotation
  • several dataProvider per one Test in TestNG
  • Cannot save model when using ember render helper
  • joining two bezier curves
  • Implementation of State Monad
  • Excel's Macro-Recorder usage
  • How to add git credentials to the build so it would be able to be used within a shell code?
  • Groovy: Unexpected token “:”
  • Replace value with Factor in r data.table
  • How to create a file in java without a extension
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • MySQL WHERE-condition in procedure ignored
  • Convert array of 8 bytes to signed long in C++
  • How to make Safari send if-modified-since header?
  • VBA Convert delimiter text file to Excel
  • How to pass list parameters for each object using Spring MVC?
  • Understanding cpu registers
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • JaxB to read class hierarchy
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices