Ask Your Question

How to use SUMIF when condition has a function?

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

migueldealmeida gravatar image

Consider the follow example: image description

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?


edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-03-01 14:08:21 +0200

See @karolus's answer here.

edit flag offensive delete link more


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

migueldealmeida gravatar imagemigueldealmeida ( 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.

migueldealmeida gravatar imagemigueldealmeida ( 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?

Mike Kaganski gravatar imageMike Kaganski ( 2017-03-01 15:53:33 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 222 times

Last updated: Mar 01 '17