calculate count of certain cells using vba in excel

Question

I have build a quiz and displaying score using some checks and output cells have "1" if ans correct and "0" if wrong.

to calculate score I use the function

=COUNTIF(S7:S158,1)

Problem is the score gets updated as soon user starts writing ans.

I want to have a Macro SUBMIT(using form control) which when clicked gives the score.

what should i write in Sub SUBMIT() so as to execute the above function

Dmitry Pavliv · Accepted Answer · 2013-12-28 22:08:16Z

1

You can write fomula:

=IF(COUNTA(S7:S158)=x;COUNTIF(S7:S158,1);"")

where x is a number of questions (for example 100). So, function COUNTA counts number of non empty cells in the range, and if the user ansered on all questions, condition COUNTA(S7:S158)=x becames true and the COUNTIF(S7:S158,1) calculated, otherwise an empty string is displayed

answered Dec 28, 2013 at 22:08

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pankaj Jaju · Accepted Answer · 2013-12-28 20:35:32Z

0

You can set the following code in Workbook_Open

Sheet1.EnableCalculation = False

In Sub Submit()

Sheet1.EnableCalculation = True
Sheet1.Calculate

For more, see this

answered Dec 28, 2013 at 20:35

Pankaj Jaju

5,5112 gold badges29 silver badges42 bronze badges

Comments

Robert Co · Accepted Answer · 2013-12-28 21:05:50Z

0

I'm a developer and I resort to macro only when all options are exhausted. This is mainly for the benefit of people that might want use/reuse it for other purposes. But that's just my preference.

You can have a cell with a label of Show Score and accepts Yes/No and put an IF around your =COUNTIF(S7:S158,1).

answered Dec 28, 2013 at 21:05

Robert Co

1,7358 silver badges14 bronze badges

1 Comment

teylyn Over a year ago

Uhm, this is really basic Excel. Type "Show Score" into one cell. That's your label. Next to it, use data validation with the list option and enter yes and no as two values separated with a comma like "yes,no".

Collectives™ on Stack Overflow

calculate count of certain cells using vba in excel

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related