Sum excluding some invalid data

%3CLINGO-SUB%20id%3D%22lingo-sub-3100359%22%20slang%3D%22en-US%22%3ESum%20excluding%20some%20invalid%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3100359%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20quick%20help.%3C%2FP%3E%3CP%3EI%20need%20to%20create%20a%20spreadsheet%20that%20will%20sum%20some%20values%20but%20I%20need%20to%20exclude%20some%20rolls%20as%20a%20requirement.%3C%2FP%3E%3CP%3ECan%20you%20help%20me%20with%20it%3F%3C%2FP%3E%3CP%3EThis%20is%20an%20example%20(but%20in%20my%20case%2C%20is%20a%20lot%20of%20data)%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EID's%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EDescription%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EContent%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EValue%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3ETest%20row%201%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EValid%20Content%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3ETest%20row%202%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EValid%20Content%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3ETest%20row%203%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EInvalid%20Content%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E30%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3ETest%20row%204%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EValid%20Content%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E40%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3ETest%20row%205%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EInvalid%20Content%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E50%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E...%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20example%2C%20the%20only%20values%20that%20I%20want%20to%20sum%20are%20the%20valid%20ones%20and%20transform%20this%20sum%20into%20a%20percentage.%3C%2FP%3E%3CP%3EThanks%2C%20guys!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3100359%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3100631%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20excluding%20some%20invalid%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3100631%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1293767%22%20target%3D%22_blank%22%3E%40Tati_Guerzoni%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(NOT(ISNA(C2%3AC25))*D2%3AD25)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20with%20this%20formula%20if%20the%20invalid%20content%20is%20an%20NA%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(IF(ISERROR(C2%3AC25)%2C0%2C1)*D2%3AD25)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20maybe%20with%20this%20formula%20to%20exclude%20any%20error.%20Enter%20this%20formula%20as%20arrayformula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3100690%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20excluding%20some%20invalid%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3100690%22%20slang%3D%22en-US%22%3EHi%20Use%20sumif%20function%2C%20column%20C%20(content)%20and%20D%20(value)%20%3DSUMIF(C2%3AC6%2C%22Valid%20Content%22%2CD2%3AD6)%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi 

I need quick help.

I need to create a spreadsheet that will sum some values but I need to exclude some rolls as a requirement.

Can you help me with it?

This is an example (but in my case, is a lot of data)

ID'sDescriptionContent Value
1Test row 1Valid Content 10
2Test row 2Valid Content20
3Test row 3Invalid Content30
4Test row 4Valid Content40
5Test row 5Invalid Content50

... 

 

In this example, the only values that I want to sum are the valid ones and transform this sum into a percentage.

Thanks, guys!

 

 

3 Replies

@Tati_Guerzoni 

=SUMPRODUCT(NOT(ISNA(C2:C25))*D2:D25)

 

Maybe with this formula if the invalid content is an NA error.

 

=SUMPRODUCT(IF(ISERROR(C2:C25),0,1)*D2:D25)

 

Or maybe with this formula to exclude any error. Enter this formula as arrayformula with ctrl+shift+enter if you don't work with Office365 or 2021.

 

 

Hi Use sumif function, column C (content) and D (value) =SUMIF(C2:C6,"Valid Content",D2:D6)

@Jihad Al-Jarady 

Cosmetic comment. Since % is required, perhaps

=SUMIF(C2:C6,"Valid Content",D2:D6) / SUM(D2:D6)