# Count with either/or

I'm counting entries from a separate spreadsheet, and have figured out how to do that, e.g., =COUNTIFS(Catalog.E4:E6000,"x",Catalog.G4:G6000,"x",Catalog.M4:M6000,"x").

However, in some instances, there are other entries in the required columns beside "x", and I want to include all entries. So, in the above example, in "Catalog.M4:M6000" I need to include not only "x", but also "D" and "T". How do I adjust the formula to include all those entries?

edit retag close merge delete

Sort by » oldest newest most voted

=SUMPRODUCT(Catalog.E4:E6000="x";Catalog.G4:G6000="x";OR(Catalog.M4:M6000="x";Catalog.M4:M6000="D";Catalog.M4:M6000="T"))

more

Did you try

=COUNTIFS(Catalog.E4:E6000,"x",Catalog.G4:G6000,"x",Catalog.M4:M6000,"x",Catalog.M4:M6000,"D"",Catalog.M4:M6000,"T").


You could possibly also use a regular expression.

If this answer helped you, please accept it by clicking the check mark ✔ to the left and, karma permitting, upvote it. If this resolves your problem, close the question, that will help other people with the same question.

more

This solution will not work if there are multiple characters in the same cell; I read the question initially as meaning there would be only one character per cell.

( 2018-11-28 01:22:04 +0100 )edit

Thank you for your input. I also discovered that, to include all entries in a column (in this case, "x", "D", "T"), I just needed to replace the "x" in my original formula with "<>".

more

The string <> (without value) as criteria for SUMIF(S) means "not equal to empty string". So if you need to filter out empty cells, use it.

If you need to include all values possible in a column (including empty), then simply don't apply any condition to the column at all, i.e. remove the two arguments for that column from the formula.

( 2018-11-28 06:28:43 +0100 )edit