COUNTIF Over Multiple Sheets

ratedr95

New Member
Joined
Oct 5, 2016
Messages
7
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.

Code:
=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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
@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

New Member
Joined
Oct 5, 2016
Messages
7
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top