PDA

View Full Version : Solved: Count Based on two Criteria



lucas
12-31-2009, 11:04 AM
Weak on formula's and will work with a vba solution too.

I'm trying to look at two columns on sheet1 and count based on meeting two criteria.

The criteria are ages and tenure. Some are not just ranges such as 20-30, they are <20 for example.

see attached for further explaination.

If I can work out one cell for a formula, I can get it from there.....

Any pointers appreciated.

Bob Phillips
12-31-2009, 11:19 AM
Steve,

I changed some of the headings (LT30 became 0-30) and used

=SUMPRODUCT(--(Sheet1!$A$2:$A$10>=--LEFT($B4,FIND("-",$B4)-1)),
--(Sheet1!$A$2:$A$10<=--MID($B4,FIND("-",$B4)+1,10)),
--(Sheet1!$B$2:$B$10>=--LEFT(C$3,FIND("-",C$3)-1)),
--(Sheet1!$B$2:$B$10<=--MID(C$3,FIND("-",C$3)+1,10)))

lucas
12-31-2009, 11:28 AM
Bob, that's fantastic and very quickly delivered.

I foolishly forgot to mention that this was sample data and it needs to read sheet 1 to the bottom of the column:

Sheet1!$A$2:$A$10
can I change these references to:

Sheet1!$A:$A

Bob Phillips
12-31-2009, 11:42 AM
Not unless you have Excel 2007. Otherwise you would need to accomodate the max number of cells, or use a dynamic range name (well, two).

lucas
12-31-2009, 11:50 AM
It's for 2003. I'm trying some things based on your contribution......

Not having any luck yet but will keep working on it...

Bob Phillips
12-31-2009, 11:53 AM
Try this

lucas
12-31-2009, 12:00 PM
That's exactly where I was headed after your suggestion. Two dynamic named ranges.

Seems to work just as expected. Will try to break it and let you know.
I was pretty sure that sumproduct was a good way to work with what I had.

Thank you very much for your help.

Marking this solved.

Bob Phillips
12-31-2009, 12:26 PM
It was either SUMPRODUCT or an array formula.