Return a range of cells that meets a criteria

**bgallagher1** · 08-13-2010, 05:34 PM

Hello,

Problem I can't figure out:

In column A I have a list of "created on" dates for support cases in chronological order.
In column E I have a list of the "subject" the case was in regards to.

I am trying to devise a formula that will examine the dates in column A that match a given time period, and spit out the range of column E that corresponds with the records.

For instance, say im looking for the range of subjects that correspond to support cases opened in January 2010. The formula would need to determine the range of cells that meet those criteria, say it finds A2:A7, and then change the A's to E's, which would tell me the range of subjects of cases opened in Jan 2010, E2:E7.

This is part of a larger formula I'm trying to figure out to determine the most common support subject (the MODE of subjects I suppose you could call it) for a given time period...I'll spare the details of that until I can figure this out and tinker with the larger formula for a bit!!

Any help would be much appreciated!!

**Richard Buttrey** · 08-13-2010, 06:09 PM

Hi,

Sounds like you should be using an =COUNTIFS() function.

Try this first and post back if it's not giving you the answer you want.

Rgds

**bgallagher1** · 08-13-2010, 08:47 PM

That very well may be a part of it, but I need the formula to return a range of cells, that is my difficulty --- E3:E19 for example. This range would then go in to a separate formula that would be used to calculate the mode of support case subjects on a per month/quarter/year basis.

How can I use a countifs to return a range? I use that quite often to count cells that contain certain criteria, but never to return a range.

**Richard Buttrey** · 08-14-2010, 04:33 AM

Hi,

You can't. I suggest you check out the Data Filtering functionality. Data Filter Advanced filter and copy to another range may be what you want, and of course you'll need to define a criteria range that the filter can use.

Rgds