Ask Your Question

# How to use SUMIF when condition has a function?

Consider the follow example:

I want the SUMIF of Cell C8 to be "sum the price, if the category is equal to B8". To individually test equality of each cell in column A I would say, for example for cell A1: EXACT(A1,B8) But whenever I want to apply this sort of thinking within a SUMIF I don't know how to represent "Ax" in the conditional inside the SUMIF.

The only way I see to do this is to create yet another auxiliary column "is exact" to perform the Exact function, and then use that column within the SUMIF conditional.

Is there a simpler alternative that doesn't require an auxiliary column?

Thanks!

edit retag close merge delete

## 1 Answer

Sort by » oldest newest most voted

See @karolus's answer here.

more

## Comments

Thanks! I failed to see that second answer, I'd accepted "the next best thing"!

( 2017-03-01 15:37:31 +0200 )edit

Actually, not sure how this would work (given we're using the function EXACT here). I was trying =SUMPRODUCT(EXACT(A1:A100,B8),D1:D100) without luck. Any idea how to correct it? EDIT: This is the correct formula, I had an issue where the formula was being applied to a cell in D, yielding an error.

( 2017-03-01 15:42:16 +0200 )edit

=SUMPRODUCT(A3:A6=$B$8;D3:D6) And isn't your D8 cell in which you are putting the example formula included into the addition range itself?

( 2017-03-01 15:53:33 +0200 )edit

## Stats

Asked: 2017-03-01 13:58:37 +0200

Seen: 222 times

Last updated: Mar 01 '17