CountIf should work across sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

the_thinker97@hotmail.com - 21 Oct 2006 19:02 GMT

The formula =COUNT(Sheet2:Sheet6!A1) works fine,
as does the formula =COUNTIF(A1:A6,"<4")

but the formula =COUNTIF(Sheet2:Sheet6!A1,"<4") does not. If there anyway
of counting across sheets only when a particular condition applys?

Reply to this Message

Bob Phillips - 21 Oct 2006 20:25 GMT

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&{1,2,3,4,5,6}&"'!A1"),"<4"))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

> The formula =COUNT(Sheet2:Sheet6!A1) works fine,
> as does the formula =COUNTIF(A1:A6,"<4")
[quoted text clipped - 8 lines]
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.

http://www.microsoft.com/office/community/en-us/default.mspx?mid=2cb14e2c-b5
67-4936-ad4e-178a2a427211&dg=microsoft.public.excel.worksheet.functions

Reply to this Message

romelsb - 21 Oct 2006 23:39 GMT

excuse me Bob....Mr thinker thats a good one...but please dont delete any
sheet in that range or even rename the sheet...good luck...more power Bob n
Thinker

> =SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&{1,2,3,4,5,6}&"'!A1"),"<4"))
>
[quoted text clipped - 22 lines]
> http://www.microsoft.com/office/community/en-us/default.mspx?mid=2cb14e2c-b5
> 67-4936-ad4e-178a2a427211&dg=microsoft.public.excel.worksheet.functions

Reply to this Message