How to use SUMIF when condition has a function?

English
, , ,
#1

Consider the follow example:

image description
image description788×202 19.5 KB

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!

#2

See @karolus’s answer here.

#3

Thanks! I failed to see that second answer, I’d accepted “the next best thing”!

#4

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.

#5

=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?

#6

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.