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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
@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))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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
Back
Top