=COUNTIFS with condition based on length of text string

**sunsoar77** · 10-24-2012, 01:41 AM

I've built a formula to count transctions that match one department, fall between two dates and are over a certain threshold (in this example its $100, but in spreads sheet it's a variable amount).

Formula:

Please Login or Register  to view this content.

What I'd like to do is add an additional count criteria, when the length of text in a cell in the same count row falls between two values.

I'm haven't used a whole lot of text based formula's but I'm finding they tend to return TRUE/FALSE as soon as an operator is input, which is throwing out my countifs (I think?!?). I can get the result I want using the following formula for an individual cell

Formula:

Please Login or Register  to view this content.

What I'd like is a way to build that into the countifs above. I'd prefer not to 'hack' it together with another column and add Transactions!M:M,"1" to my countifs.

Suggestions appreciated!

**e.isayev** · 10-24-2012, 02:53 AM

An array formula
=SUM((MONTH(B3:B34)=10)*(E3:E34="B")*(LEN(F3:F34)>=4)*(LEN(F3:F34)<=6)*(I3:I34>100))

**sunsoar77** · 10-25-2012, 12:49 AM

Hi eisayew,

That works well for the sample. The data I'm using is around 500 rows currently, and is added to every week with an additional 20 - 50 rows inserted at the top of the sheet. I noticed the array forumla won't work with column references (ie. B:B or E:E).

Is there a way to work around this?

**FDibbins** · 10-25-2012, 12:59 AM

extend the end of the references to 10 000? that should keep you for a while

**sunsoar77** · 10-25-2012, 01:54 AM

My team are copy data from a system produced report that only holds 2 or 3 weeks of data, and we are tracking things over a 12 month period.

So we are selecting the 'new' rows of data from the system report to copy, and the 'Insert' the copied rows to the top of the sheet which pushes the cell references for the array down by however many rows I insert.

I think I've just stumbled on a solution - If the rows are 'inserted' inside the existing array references it extends the array. So if I start my array from B1:B10000 we can insert as many rows as we like.

I'll test this with my test case next monday when I get a new lump of data.

**FDibbins** · 10-25-2012, 02:02 AM

good thinking

**sunsoar77** · 10-25-2012, 02:25 AM

Almost there (this post is more for anyone who googles this!)

The array's don't like it when there is a cell in array that doesn't match the formatting. In this case (my worksheet has about 6 rows with heading data) it hits the 'DATE' cell (B2) in example and falls over.

My inelegant solution - insert row with dummy data about where actual data starts and hide it and set this row as start of my array. When team members insert copied rows they select the start of the real data it leaves the array starting in the 'dummy data' resulting in forumla's and arrays staying intact!