Formula for a complex count criteria

  • Thread starter Thread starter Risky Dave
  • Start date Start date
R

Risky Dave

Hi,

Given a data set like this:

A B C D
ID 3 Pending date Text
ID 1 Closed date Text
ID 2 Open date Text
ID 4 Transfer date Text

Column A contains unique ID numbers - these are not and cannot be sorted
Column B can be one of four possible values - I have no way of knowing which
one applys to each ID
Column C contains the date the item was added to the list - this is not
necessarily unique to each item
Column D is a free text field

What I want to do is a count of the number of lines that fulfil the
following criteria:
a) Column B = "Open"
b) Column C is less than or equal to today's date minus 30 (ie. it was added
in the last 30 days)
c) Column D contains the text "New item" (it's up to me to make sure this
text is actually there if appropriate, I just want to be able to search for
it)

I don't know how many lines I will have to search, but I can set a maximum,
if necessary.

If anyone can suggest a formula that will doo this for me, I would much
appreciate it. Also happy to use VB if that's easier (i understand it, just
wouldn't really know how to write it - still learning!).

This is in XL2003, if that makes a difference.

TIA

Dave
 
Try this:

=SUMPRODUCT((B1:B100="Open")*(C1:C100>=TODAY()-30)*(C1:C100<=TODAY())*(D1:D100="New
item"))

I've assumed you have 100 rows of data - adjust this if you have more,
but you can't use full-column references prior to Excel 2007.

Hope this helps.

Pete
 
You will have to adjust the range of the formula for each condition below to
include the number of rows required so for 1000 rows you would substitute
B2:B5 with B2:B1001 etc.


=COUNTIFS(B2:B5,"=Open",C2:C5, "<=" & TODAY()-30,D2:D5, "=*New Item*")
 
Thanks Pete I had not realised it was a new function
COUNTIFS is only available in XL2007 - OP stated XL2003.

Pete
 
Pete,

Thanks. this does what I need but for one little thing that I obviously
didn't explain clearly :-(

The text search piece might contain "New item" but will almost certainly
contain other text as well, so what I need is to do something like:

=SEARCH("New item",D1:D100)

but obviously, SEARCH doesn't work on range of cells :-(

Is there a way around this?

TIA

Dave
 
Try it this way:

=SUMPRODUCT(--(B1:B100="Open"),--(C1:C100>=TODAY()-30),--
(C1:C100<=TODAY()), --(ISNUMBER(SEARCH("New item",D1:D100))))

Hope this helps.

Pete
 
The text search piece might contain "New item" but will almost
certainly contain other text as well, so what I need is to do
something like:

=SEARCH("New item",D1:D100)

but obviously, SEARCH doesn't work on range of cells :-(
Click to expand...

Instead of

...*(D1:D100="New item")...

try

...*(LEN(D1:D100)>LEN(SUBSTITUTE(D1:D100;"New item";)))...

Probably there are still better ones :-)
 
=SUMPRODUCT((B1:B100="Open")
*(C1:C100>=TODAY()-30)
*(C1:C100<=TODAY())
*isnumber(search("new item",D1:D100)))
 
Back
Top