Counta / Countif

[mandek]

Hi everyone,

I am new to this forum and beginner with VBA.

If someone would be able to help would be great.

I got one sheet as a “StatusSheet” and I want to count ( yes, no, N/A ) on separate sheets ( total sheets 21, Area_1, Area_2, Area_3, ….Area_21).

The Yes, No, N/A value always will be saved in the same column, between AQ4:AQ200.

At them moment I got

=SUM((COUNTIF(‘Area_1’!$AQ$4:$AQ$200,"Yes"))) ‘to count YES value
=SUM((COUNTIF(‘Area_1’!$AQ$4:$AQ$200,"No"))) ‘to count No value
=SUM((COUNTIF(‘Area_1’!$AQ$4:$AQ$200,"N/A"))) ‘to count N/A value

And

=COUNTA(‘Area_1’!$AQ$4:$AQ$200) ‘ to count total in the column.

I would like to have this in macro and put this in the loop.

is any chance to do this ?

Regards
M

[Norie]

Try this.

1 In the status sheet put Area_1 in A2 and copy down to A22.

2 In B1 put 'Yes', in C1 put 'No' and in D1 put 'N/A'.

3 In B2 put this formula.

Please Login or Register  to view this content.

4 Copy the formula across and down to B2:D22.

[mandek]

Hi Norie,

Thank you for reply.

Sorry but I am looking for something different.
I want to count Yes, No, N/A on each sheet (sheet Area1, Area2, Area3, Area4, Area5, Area6, Area7, Area8, Area9, Area10, Area11,etc.) and display value ( number ) on StatusSheet.

I think I would need to use loop ( For) and specify the range, but how I can specify the sheets range ( Area1 to Area13).

I have done small example, please see attached JPGs.

Please let me know if would be possible to do this?

Regards
M

[Norie]

What I posted will count Yes, No and N/A on sheets Area_1 to Area_21.

If you want to have the results in a specific range then it would be easy to adjust the references in the formula I posted.

You would need to adjust some other things too, for example in the original post the sheet names had an underscore but now you seem to indicate they don't but as long as you have the actual sheet names on your sheet that doesnt' matter.

The other thing that might need adjusted is the column, in the original post it was column AQ but in the image you've posted it appears to be column S.

By the way, even if you wanted code to do this you wouldn't need a loop.

[mandek]

Hi Norie,

Thank you for your help.
Yes, you are right and the code works fine.

The print screens what I used were just for reference.

I got one more last ( hope ) question.

How to count all total number of YES, NO, N/A in the columns in Area1 to Area13?

Thank you for your help.
M

[mandek]

Done something like this.
doesn't looks nice but does the job
=COUNTIF(INDIRECT($A2 & "!$s$1:$s$200"), B$1)+COUNTIF(INDIRECT($A2 & "!$s$1:$s$200"), C$1)+COUNTIF(INDIRECT($A2 & "!$s$1:$s$200"), D$1)

[mandek]

Looks like the problem begin when instead of name Area1 we want to put space between Area and 1 “Area 1”.
Is there any way or the name of the spreadsheet always need to be one word?

Regards
M

[Norie]

No you can have a space in the name, just need to adjust the formula slightly to enclose the sheet name in single quotes.

Please Login or Register  to view this content.

As for the previous question about counting the total number of Yes, No and N/A, why not just sum the result of the three existing formulas for each sheet?

For example in E2 and copied down.

Please Login or Register  to view this content.