Ask Your Question
0

Count with either/or

asked 2018-11-27 01:31:37 +0100

downiepaul gravatar image

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 flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2018-11-27 06:34:11 +0100

Mike Kaganski gravatar image

updated 2018-11-27 06:34:34 +0100

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

edit flag offensive delete link more
0

answered 2018-11-27 03:24:02 +0100

robleyd gravatar image

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.

edit flag offensive delete link more

Comments

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.

robleyd gravatar imagerobleyd ( 2018-11-28 01:22:04 +0100 )edit
0

answered 2018-11-27 21:44:01 +0100

downiepaul gravatar image

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 "<>".

edit flag offensive delete link more

Comments

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-11-28 06:28:43 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-11-27 01:31:37 +0100

Seen: 28 times

Last updated: Nov 27 '18