How to count rows based on multiple criteria

**jackb1117** · 03-27-2009, 01:52 AM

I am trying to design a formula that will count the number of rows that meet certain criteria. The criteria are that status equals either 'active' or 'inactive', and that the region equals a certain region(s).

I have attached a sheet that has a sample of data (data tab), and my formula on the results tab. If, for instance, I want to find all 'Active' status with a region of 'South', I have been using this formula (Note: I am using named ranges here): =SUMPRODUCT((Status="Active")*(ISNUMBER(SEARCH("South",Region)))) which works as expected.

Where I am running into trouble is when I try to incorporate an 'OR' into this. For instance, all 'Active' accounts where the region equals 'South' OR 'West'. I have tried incorporating an OR into the formula in many different places, but to no avail. And when I use: =SUMPRODUCT((Status="Active")*(ISNUMBER(SEARCH("South",Region)*(ISNUMBER(SEARCH("West",Region)*))))) ) , it functions like an 'AND' statment.

Any help would be GREATLY appreciated.

Cheers,

Jack

**DonkeyOte** · 03-27-2009, 05:03 AM

To do an OR clause you would generally use + operator, ie

=SUMPRODUCT((Status="Active")*(ISNUMBER(SEARCH("South",Region))+ISNUMBER(SEARCH("West",Region))))

However looking at your data this may not be viable approach given your Data sheet may include both Regions within the same string.. ie where Region cell contains "West" AND "South" this will count as 2 rather than 1 - ie West = 1 + South = 1 ... what are you expected results where this occurs - ie 2 or 1 ?

**jackb1117** · 03-27-2009, 07:09 AM

DonkeyOte,

Thanks for the reply. In the case where both regions are in the cell (ie South AND West), I would want '1' as the result. Basically trying to find all records that are active, and have either 'South' or 'West' (or both).

Jack

**DonkeyOte** · 03-27-2009, 08:23 AM

Jack, to be honest I'm having a bit of a brain freeze with this one given your sample file... I will keep tinkering to see if I can come up with a viable approach - in the meantime someone else may well come up with the solution.

**DonkeyOte** · 03-27-2009, 08:36 AM

Here is a VERY ugly method -- not flexible really... it's all quite awkward using a Sumproduct approach ... I'm wondering if one of the guru's here can come up with something elegant and/or I'm missing something obvious ?

Note: I also altered the way you were creating your named ranges, if you do Insert -> Name -> Define you can see how I've established the ranges for Region & Status.