• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trouble with SUMIF's formula and multiple conditions

G

greykitten

Member
Hello,
I'm trying to use a SUMIF's formula to calculate a sum if a column contains the following values, "EWS Duplicate Detection", "EWS/ANF" or "EWS/RNF". I'm not sure if the SUMIF's formula is the right one for this because SUMIF's require that all conditions be met. The formula needs to take the sum of all the values in an adjoining column if the value is either EWS Duplicate Detection, EWS/ANF or EWS/RNF. It doesn't have to meet all three criteria; it only needs to be one of them. Currently I have this:

=SUMIFS($I$2:$I$23769,$A$2:$A$23769,"EWS Duplicate Detection",$J$2:$J$23769,"Closed")

I tried adding an additional condition with OR but it didn't work (see below).

=SUMIFS($I$2:$I$23769,$A$2:$A$23769,"EWS Duplicate Detection",$J$2:$J$23769,"Closed")*OR($I$2:$I$23769,$A$2:$A$23769,"EWS/ANF",$J$2:$J$23769,"Closed")

Can someone please help me? Thank you in advance for your help.
GreyKitten
 
Hi @greykitten

You can use this formula:
=SUM(SUMIFS($I$2:$I$23769,$A$2:$A$23769,{"EWS Duplicate Detection";"EWS/ANF"},$J$2:$J$23769,"Closed"))

Please comment! Blessings!
 
Hi John,
Do you know how I could adjust that formula to count the number of times that those three values appear that column? I tried this =COUNTIFS($A$2:$A$23769,{"EWS Duplicate Detection";"EWS/ANF";"EWS/RNF"},$J$2:$J$23769,"Closed") but it produced a small number nowhere near the real population size. Not sure if I should combine with a SUMIFS or maybe even a COUNTA formula.

Thanks so much for your help!
GreyKitten
 
=SUM(((a2:a23769="EWS Duplicate Detection")+(a2:a23769="EWS/ANF")+(a2:a23769="EWS/RNF"))*(i2:i23769)*(j2:j23769="closed"))

think this should also work...with CTRL+Shift+enter
 
Last edited:
=SUM(((a2:a23769="EWS Duplicate Detection")+(a2:a23769="EWS/ANF")+(a2:a23769="EWS/RNF"))*(j2:j23769="closed")
with Ctrl +shift +enter

you could use this to count
 
Last edited:
Hi ,

Wrapping your formula within a SUM , as in :

=SUM(COUNTIFS($A$2:$A$23769,{"EWS Duplicate Detection";"EWS/ANF";"EWS/RNF"},$J$2:$J$23769,"Closed"))

will work. This kind of SUM wrapper is required for the SUMIF(S) family of functions when a literal array is used inside.

Narayan
 
You must log in or register to reply here.
Back
Top