Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / January 2008

Tip: Looking for answers? Try searching our database.

count(if(... using array formula: can I use a named range in my ca

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
katy - 15 Jan 2008 01:19 GMT
I'm using an array formula to give a count of rows from a big data table.  I
want a count of all records which are in a certain month, and in a certain
region.

In the Data sheet Col A contains the month and Col C is the region.  In
another sheet, I have a result table with this array formula:
=count(if(Data!$A$2:$A$1000="January",if(Data!$C$2:$C$1000="Canterbury",Data!$A$2:$A$1000)))
and it works fine; I get a count of all the January records from the
Canterbury region.

But, how can I improve my array formula so that I get multiple regions in
one count? i.e. I want a count of all the January records from the
Canterbury, Nelson and Marlborough regions.

I can make my formula work if I change it to:
=count(if(Data!$A$2:$A$1000="January",if(Data!$C$2:$C$1000="Canterbury",Data!$A$2:$A$1000)))+count(if(Data!$A$2:$A$1000="January",if(Data!$C$2:$C$1000="Marlborough",Data!$A$2:$A$1000)))+count(if(Data!$A$2:$A$1000="January",if(Data!$C$2:$C$1000="Nelson",Data!$A$2:$A$1000)))

but this is horribly long and unwieldy and gets out of hand when I want to
group together more regions!

I've tried doing this, but it didn't work (it counted ALL the rows in the
data table):
=count(if(Data!$A$2:$A$1000="January",if(OR(Data!$C$2:$C$1000="Canterbury",Data!$C$2:$C$1000="Marlborough",Data!$C$2:$C$1000="Nelson"),Data!$A$2:$A$1000)))

I also thought I might be able to do it using a named range, but this didn't
work either.  I created a range called UpperSouth which contained the values
Canterbury, Marlborough and Nelson, entered the text UpperSouth into cell B1,
and then I changed my array formula to:
=count(if(Data!$A$2:$A$1000="January",if(Data!$C$2:$C$1000=INDIRECT(B1),Data!$A$2:$A$1000)))
but this didn't work.

Can anyone help me with a way to incorporate different regions into one count?
(PS I know a Pivot Table would be great, but the problem's actually a bit
more complicated than I've described and I'd rather do it by an array formula
if I can!)

Thanks
katy - 15 Jan 2008 02:13 GMT
I worked it out!  Will leave the answer here in case it's useful to others.  
The formula that works is:

=count(if(Data!$A$2:$A$1000="January",Match(Data!$C$2:$C$1000,UpperSouth,0)))

> I'm using an array formula to give a count of rows from a big data table.  I
> want a count of all records which are in a certain month, and in a certain
[quoted text clipped - 33 lines]
>
> Thanks
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.