Formula for a complex count criteria

[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

 

[Pete_UK]

Try this:

=SUMPRODUCT((B1:B100="Open")*(C1:C100>=TODAY()-30)*(C1:C100<=TODAY())*(D1100="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

 

[GerryGerry]

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,D25, "=*New Item*")

 

[Pete_UK]

COUNTIFS is only available in XL2007 - OP stated XL2003.

Pete

 

[GerryGerry]

Thanks Pete I had not realised it was a new function
COUNTIFS is only available in XL2007 - OP stated XL2003.

Pete

 

[Risky Dave]

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",D1100)

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

Is there a way around this?

TIA

Dave

 

[Pete_UK]

Try it this way:

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

Hope this helps.

Pete

 

[Alexander Wolff]

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",D1100)

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

Instead of

...*(D1100="New item")...

try

...*(LEN(D1100)>LEN(SUBSTITUTE(D1100;"New item"))...

Probably there are still better ones

 

[Dave Peterson]

=SUMPRODUCT((B1:B100="Open")
*(C1:C100>=TODAY()-30)
*(C1:C100<=TODAY())
*isnumber(search("new item",D1100)))