Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there any way for me to add a bunch of cells that only have a 1 in them? If they have a 0 in them, then this should not be added to the total. This should also work if the list is filtered too.

share|improve this question

closed as not a real question by Dave, slhck Sep 27 '12 at 6:54

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

I think that you mean that you want to count the number of cells with 1 in them?


Will give you a count of the number of cells in column A containing a number greater than zero, change the test to =1 to be more specific.

share|improve this answer

Yes. Just add them all since 0 will not affect the value?

share|improve this answer
well that is pointless, cos if I do =SUM(F2:F49) it still adds up all of the cells with 0 in them too, I don't want them added!!! – harry Jul 18 '12 at 15:15
@harry If you add 0 to a sum, it won't add anything. Can you clarify your question by maybe including a more specific example? – slhck Jul 18 '12 at 15:17
@slhck Do you think harry might accept the answer =SUMIF(F2:F49,"1")! – pnuts Sep 26 '12 at 23:04
@pnuts No idea. The OP never came back to clarify, so I've closed that question now. – slhck Sep 27 '12 at 6:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.