I have used drop down lists in one spreadsheet to provide yes or no answers to some simple questions I need to pose to members of my team. On another spreadsheet in the same workbook, I use the following formula, =IF(Sheet1!B81="Yes", "1","0"), to convert the yes and no answers into the values "1" and "0". My problem is, I am trying to sum up the total number of 1s to come up with a sum of answers to each question. The problem is I can't figure out how to sum the values from the formula I am using. I used =Sum(B2:B100) but it returns 0 because I assume it's looking at the formula in the column and not the results of the formula in the column. I feel there is a simple fix but I can't figure it out. Any ideas?

Why complicate things.

Use =COUNTIF() function on your YES/NO cells directly:=COUNTIF(Sheet1!B2:B100,"Yes")

MIKE

Do you by any chance happen to be kilkenney? Your question is eerily similar to this thread:

http://www.computing.net/answers/of...

In any case, when posting in a Help forum, please try to use a subject line that is relevant to the contents of the post. If everyone used a generic subject line such as "Microsoft Excel Formulas" we wouldn't be able to tell one question from another.

I have changed the Subject Line of this thread to make it more relevant.

DerbyDad03

Office Forum Moderator.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

re: "it returns 0 because I assume it's looking at the formula in the column and not the results of the formula in the column"Your assumption is incorrect. What good would formulas be if you couldn't use the values that they return in other calculations?

It is returning 0 because you used quotes around your values:

=IF(Sheet1!B81="Yes", "1","0")

Excel interprets anything within quotation marks to be text, not numbers, and you can't SUM text values.

In other words, "1" is not the same as 1.

If you use this instead, the SUM formula will work:

=IF(Sheet1!B81="Yes", 1, 0),

That said, Mike's suggestion of using COUNTIF to eliminate the IF function is certainly more efficient, unless of course you need the 1's and 0's for something else.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History