# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Count Cells on different sheets

## keith6292

I need to count cells on 3 different worksheets within 1 workbook which contain certain criteria.  is it possible to do this with a countif formula?

----------


## jj72uk

Yes...

=COUNTIF(Sheet2!A1:A13, "*")

Where * is what you are to count and the range has *SHEET NAME!* before hand..

----------


## mohitspamz

Hey!

Maybe something like this:

=SUM(COUNTIF(A1:A17,">10"),COUNTIF(Sheet2!A1:A15,">10"))

Hope it gives u some start...

Regards
Mohit

----------


## DonkeyOte

As Mohit has illustrated you will need to combine three COUNTIF functions, if you have a limited & fixed number of sheets to deal with then this should be the approach you adopt... unfortunately you can not conduct 3D COUNTIF, ie COUNTIF(Sheet1:Sheet3!A1:A10,">0") won't work.... "clever" formulae in this instance are volatile & expensive, simplicity is best.

----------


## keith6292

I Cant seem to figure it out.  here is an example of what i wanna do. Look at FIWP sheet.

EXAMPLE.xls

----------


## mohitspamz

Umm..
I didnt get ur requirement exactly but if ur looking to just count B3 in FIWP columns in sheet 250,260,270 then it should be something like this...

=Sum(Countif(250!C6:C2500,$B3),Countif(260!C6:C2500,$B3),Countif(270!C6:C2500,$B3))

Regards
Mohit

----------


## jj72uk

How about

=COUNTIF('250'!C:C,B3)+COUNTIF('260'!C:C,B3)+COUNTIF('270'!C:C,B3)

----------


## gamit

1. You write sheet names in A20 to A27 cells
2. F2:F5 are the fields in every sheet to be counted
3. C2 is the criteria


=SUMPRODUCT(COUNTIF(INDIRECT(""&A20:A27&"!F2:F5"),C2))


Now if you need to count 1 cell in every sheet and keep total in main sheet 
for each entry then use

=SUMPRODUCT(COUNTIF(INDIRECT(""&BA$201:BA$231&"!"&CELL("address",O5)),"y"))
Now
- BA201 to BA231 is where you have put sheet names
- O5 is the cell in every sheet you want to count if result is y (y is not case sensitive)
- Used &CELL("address",O5)so that when you drag it down then it will check for O6 and like wise

Note : If you simply put O5 and expect it to change then it will not change

----------


## Indi_Ra

gamit,

You responded to a post 7 years old.  :Smilie:

----------


## vermayan

Still more usefull than all other solutions here.  :Smilie:

----------

