COUNTIF Over Multiple Sheets

[ratedr95]

Hi,

I am trying to write a formula to count the number 1 over a set of cells in each sheet into a totals sheet at the end.

I have named a sheet First and Last at the relevant places to help with the formula but get a #VALUE! error when I try and get the result.

=SUMPRODUCT(COUNTIFS(First:Last!AS8,"1"))

Above is the formula I am currently using, but will not work.

Unfortunately I do not have the ability to install add ons due to it being a work machine.

Thanks

 

[XOR LX]

Hi,

1) Are the values in those cells all numbers? Or might there be text and/or blanks?
2) Are there any negative numbers in those cells?
3) Are there any zeroes in those cells?

Regards

 

[Special-K99]

3D reference does not support COUNTIFS it says here.

http://www.mrexcel.com/forum/excel-questions/224433-why-am-i-getting-error-countif-3d-reference.html

 

[Aladin Akyurek]

@ratedr95

Create a range that houses the names of all of the relevant sheets. Select this range and name it SheetList via the Name Box on the Formula Bar.

Once you have done this, invoke:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!AS8"),1))

 

[ratedr95]

Hi,

1) Are the values in those cells all numbers? Or might there be text and/or blanks?
2) Are there any negative numbers in those cells?
3) Are there any zeroes in those cells?

Regards

1) All either 0's or 1's
2) No
3) As above

I can change these to OK or ERROR though

 

[XOR LX]

1) All either 0's or 1's
2) No
3) As above

I can change these to OK or ERROR though

Excellent. Which means we can avoid the volatile solution from post #4 using the much simpler:

=INDEX(FREQUENCY(First:Last!AS8,0),2)

Regards