CountIF() in Worksheet B while referencing cells in Worksheet A

[jfj3rd]

Hi everyone,

So my title might be the best way to answser my questionb ut I'll give
it a longer description here.

I don't want to clutter worksheetA with hidden fields containing
formulas that I would then call in WorksheetB.

So how do I tell CountIF to calculate specified cells in a different
worksheet?

Your help would be appreciative. I searched through the help file but
just simply don't know what I'd be looking for.

JJ

 

[Guest]

You could just Define a Named Range (Insert Menu -> Names -> Define). and
then the formula would refer to the Named Range instead of a range of cells.

example: =COUNTIF(myNamedRange,"Yes")

myNamedRange could dynamically be defined by using the formula:
=OFFSET($A$1,0,0,COUNTA('Sheet1'!$A:$A),1)

This assumes the data you are wanting the COUNTIF to look at would be in
Column A, starting in Row 1. This dynamic named range will end the first
time it encounters a blank cell, though.

I hope this helps.

Rich

 

[Carim]

Hi,

For multi criteria, take a look at sumproduct()

Cheers
Cari

 

[Bob Phillips]

have you not just tried

=COUNTIF('other worksheet name'!range,criteria)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)