Forum Discussion
Sum excluding some invalid data
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's | Description | Content | Value |
| 1 | Test row 1 | Valid Content | 10 |
| 2 | Test row 2 | Valid Content | 20 |
| 3 | Test row 3 | Invalid Content | 30 |
| 4 | Test row 4 | Valid Content | 40 |
| 5 | Test row 5 | Invalid Content | 50 |
...
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
- Hi Use sumif function, column C (content) and D (value) =SUMIF(C2:C6,"Valid Content",D2:D6)
Cosmetic comment. Since % is required, perhaps
=SUMIF(C2:C6,"Valid Content",D2:D6) / SUM(D2:D6)
=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.
