SOLVED

SUMIF criteria help

%3CLINGO-SUB%20id%3D%22lingo-sub-2362452%22%20slang%3D%22en-US%22%3ESUMIF%20criteria%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2362452%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20get%20the%20sum%20of%20numbers%20in%20column%20I%2C%20but%20exclude%20rows%20from%20that%20sum%20if%20the%20word%20%22SKIP%22%20is%20listed%20in%20Columns%20A%2C%20B%2C%20and%20C%20of%20a%20given%20row.%20I%20don't%20want%20the%20number%20excluded%20from%20the%20sum%20if%20%22SKIP%22%20appears%20in%20one%20or%20two%20of%20those%20Columns%2C%20only%20if%20it%20appears%20in%20all%20three.%20Can%20someone%20help%20me%20write%20that%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2362452%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2362494%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20criteria%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2362494%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F894985%22%20target%3D%22_blank%22%3E%40mesmrc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdjust%20the%20ranges%20if%20needed.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT(I2%3AI100%2C--((A2%3AA10%3D%22SKIP%22)%2B(B2%3AB100%3D%22SKIP%22)%2B(C2%3AC100%3D%22SKIP%22)%26lt%3B3))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2362717%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20criteria%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2362717%22%20slang%3D%22en-US%22%3Ewooh!%20hugely%20helpful%2C%20thanks%20so%20much%20Hans!%3C%2FLINGO-BODY%3E
Occasional Contributor

I'm trying to get the sum of numbers in column I, but exclude rows from that sum if the word "SKIP" is listed in Columns A, B, and C of a given row. I don't want the number excluded from the sum if "SKIP" appears in one or two of those Columns, only if it appears in all three. Can someone help me write that formula?

2 Replies
best response confirmed by mesmrc (Occasional Contributor)
Solution

@mesmrc 

Adjust the ranges if needed.

 

=SUMPRODUCT(I2:I100,--((A2:A10="SKIP")+(B2:B100="SKIP")+(C2:C100="SKIP")<3))

wooh! hugely helpful, thanks so much Hans!