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

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

Hi,

For multi criteria, take a look at sumproduct()

Cheers
Carim

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

"jfj3rd" wrote:

>
> 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
>
>
> --
> jfj3rd
> ------------------------------------------------------------------------
> jfj3rd's Profile: http://www.excelforum.com/member.php...o&userid=33503
> View this thread: http://www.excelforum.com/showthread...hreadid=532987
>
>

have you not just tried

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jfj3rd" <jfj3rd.269lfp_1145035507.2405@excelforum-nospam.com> wrote in
message news:jfj3rd.269lfp_1145035507.2405@excelforum-nospam.com...
>
> 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
>
>
> --
> jfj3rd
> ------------------------------------------------------------------------
> jfj3rd's Profile:
http://www.excelforum.com/member.php...o&userid=33503
> View this thread: http://www.excelforum.com/showthread...hreadid=532987
>