Count on multiple criteria within spcified Range ( Excel 2003)

[kgibson20]

I'm looking to do a count on dates (column D), that are based on a type (column B), fall between a specific range (column C), and it only counts the date in the called out date range from cells B2 &3. The counts will go in cells B22-25 and will change based upon the date range I have in theB2&3 cellsI've attached a file for reference. Also I would like the cell to return a blank where there are no dates that fall into the criteria. I tried to use an if(sumproduct type of funtion but it seemed too big and I wasn't able to include all criteria.

[kgibson20]

An additional piece of information that might be inportant. The #s in coulmn c will not be in order so the formula can't count on dragging a specific range, but must include the range as part of the criteria.

[MarvinP]

Try this

Please Login or Register  to view this content.

I don't understand the change in range part but the above is a good start.

[kgibson20]

Is countifs a 2003 function? It doesn't pop up on my version.

The numbers in column c will not be in order so the range will have to be called out in the formula as a criteria such as >=25, <=31.

basically the formula says count if a date is type a & is >=25, <=31 & falls in between specified dates at the top. If there is no date that meets the criteria return a blank"".

[DonkeyOte]

No, COUNTIFS is not available pre XL2007

As a basic pointer:

Please Login or Register  to view this content.

I'd suggest you split your criteria into separate cells

SUMPRODUCT is not efficient so use above in moderation and keep precedent ranges as lean as possible.

note: in your sample your ranges overlap (<=31 and >=31)

[kgibson20]

Thanks,

The formula definitley works for the example I set up. When I go to use it in my actual data it only seems to return an "A" count and ignores the rest of the criteria if I take the if statement away and if I leave it returns a blank. Basically it just returns all the A's. I'm going to continue to play with it but is there a problem with this string

Actual String:
=IF(SUMPRODUCT(('[3 STRUCTURE PROGRESS UPDATE.xls]HEADING B3'!$G$3:$G$265="A")*('[3 STRUCTURE PROGRESS UPDATE.xls]HEADING B3'!$F$3:$F$265>=$C9)*('[3 STRUCTURE PROGRESS UPDATE.xls]HEADING B3'!$F$3:$F$265<=$D9)*('[3 STRUCTURE PROGRESS UPDATE.xls]HEADING B3'!$M$3:$M$265>=C1)*('[3 STRUCTURE PROGRESS UPDATE.xls]HEADING B3'!$M$3:$M$265<=C2))=0,"",SUMPRODUCT(('[3 STRUCTURE PROGRESS UPDATE.xls]HEADING B3'!$G$3:$G$265="A")*('[3 STRUCTURE PROGRESS UPDATE.xls]HEADING B3'!$F$3:$F$265>=$C9)*('[3 STRUCTURE PROGRESS UPDATE.xls]HEADING B3'!$F$3:$F$265<=$D9)*('[3 STRUCTURE PROGRESS UPDATE.xls]HEADING B3'!$M$3:$M$265>=C1)*('[3 STRUCTURE PROGRESS UPDATE.xls]HEADING B3'!$M$3:$M$265<=C2)))


Summary string:
=IF(SUMPRODUCT((Range="A")*(Range>=$C9)*(Range<=$D9)*(Range<=C1)*(Range<=C2))=0,"",SUMPRODUCT((Range="A")*(Range>=$C9)*(Range<=$D9)*(Range<=C1)*(Range<=C2)))

[mahju]

Use DCount function or Count(If( function

Please Login or Register  to view this content.

An array function (Ctrl + Shift + Enter to confirm)

This is for for first set Range 25-31 A

Change this for other three formulas

Thanks


See attached sheet for DCount

Criteria can be on other sheet

Regards

[MarvinP]

Hi kgibson20,

I couldn't tell which version of Excel you were using as your profile doesn't specify it. Please go to your profile page and add the version of Excel you are using.

This will allow me to see what newer Excel Functions we can't use to try to answer your questions.

My formula worked great using Excel 2007 and 2010. Perhaps you should upgrade?

[romperstomper]

The question title does say Excel 2003.
Also, suggesting an upgrade so your solution works isn't particularly practical!

[MarvinP]

You assumed I read the title for version??.

You are correct as usual but I really did look at the profile and couldn't tell which version he was using.

I wonder how long we need to support versions of Excel???
To answer my own question I find:
http://support.microsoft.com/gp/lifeselectindex#E which shows

http://support.microsoft.com/lifecycle/?p1=2512

Which says Mainstream Support ended April 14th 2009 for Excel 2003 and
Extended Support will end Aug 4th 2014.

Time to upgrade?

[NBVC]

Not everyone can afford to upgrade... and not all companies are upgrading.. that doesn't mean, we as non-paid volunteer support can't help the guy who is stuck with the version he has and needs a formula to move on...

[romperstomper]

You assumed I read the title for version??.

No, I just assumed you read the title.

[MarvinP]

I'm so happy to get any answer for some of these problems, it bursts my bubble when the add-on statement is, "oh and it has to be done with 2003!"

I guess I'm looking at Product Cost = $140 for the Student Edition of Office 2010. And if our time is worth $20/hr that would be .... uh, uh, a few hours of labor.

I guess we need a thread titled "When you should upgrade your version of Excel"?

[romperstomper]

And if our time is worth $20/hr

I don't know about you, but mine's a bit more than that!

[teylyn]

I guess I'm looking at Product Cost = $140 for the Student Edition of Office 2010

Fine. But what if the OP is not a student? What if the OP is working in a corporate environment, where s/he has no control over the version being used? What if the OP is running her own business and can't afford to upgrade?

Even if Office 2010 is "only" $140 for the Student license, it's still way more than that for a full business license. If you run a small business with a few dozen employees, you'll be looking at thousands $$$ to upgrade everyone in the company. In this economic climate, upgrading may simply not be an option.

[MarvinP]

We were thinking about you this week. Did you get rattled? Are you far enough north from CC to not see damage?

I'm old enough that I've bought and learned more dead products than live ones. At least people who use Excel haven't been dumped because they haven't come out with new versions. AND with each new version things get a little easier and faster and smaller and ...

It just frustrates me when a CountIfs in 2007 will work great but the user spends 4 hours coming up with a CSE workaround with an older version. I wish managers would consider the un-productivity costs of using older products.