Countifs with different results

[puskacpj]

Hello: I am using the Countifs function to count the number of enteries in various aging buckets. What I am totally baffled about is why the two examples result in different results. I probably am missing something quite obvious but I'm going insane trying to figure it out.

=COUNTIFS(New!$C$2:$C$354,$A71,New!$J$2:$J$354,"3-Medium",New!$H$2:$H$354, ">20",New!$H$2:$H$354, "<26")

result is: 2 (wrong)

=COUNTIFS(New!$C$2:$C$354,$A71,New!$J$2:$J$354,"3-Medium",New!$H$2:$H$354, ">=21",New!$H$2:$H$354, "<=25")

result is: 1 (correct)

Don't they mean the same thing???

Thank you for any insight.

 

[p45cal]

At first glance, I'd say because there's a value in column H which is (greater than 20 but less than 21) and/or (less than 26 but greater than 25) which also fulfils the other criteria.

 

[puskacpj]

Thank you for responding. I started looking closer at the data. There are 3 date fields that I use to age. 1 is format: mm/dd/yyyy. Other 2 have the time included..short date format? Anyway I formatted those two columns as: mm/dd/yyyy and it still did not work right. I went to the Excel Options, Advanced Settings and in the When Calculating Workbook section, I selected: Set precision as displayed. That worked great. BUT, I will be distributing this code and feel it isn't real good to have to have everyone do that in Excel. I think what I need is to convert the short date format with the time to mm/dd/yyy. Any thoughts? Thank you again.

 

[puskacpj]

I figured out a way. Just added vba to set the precision and all is well - that is till the next challenge.

Range("N:N,G:G,L:L").Select
Range("L1").Activate
Selection.NumberFormat = "m/d/yyyy;@"
Range("N2").Select
ActiveWorkbook.PrecisionAsDisplayed = True

Thank you for making me 'think' and not just give up!

 

[p45cal]

I formatted those two columns as: mm/dd/yyyy and it still did not work right.

As you've discovered, formatting only changes how things are seen, not the value behind the format, which remains the same.
Now I'm inferring that column H contains dates, but you're looking for dates which have a value of 20 or so, that's the 20th Jan 1900 or thereabouts, which I doubt you're working with, so are these dates in column H differences between two other dates? That is, there's a formula in column H (or it's derived from a formula). If so we can tweak that formula to give only whole numbers.
Another solution is to decide if your original two formulae are correct. You'd normally try to make counts of buckets (bins may be a more useful term as you'll see later) with one side say > and the other <= (or of course >= and <), so that the count won't miss any values or double count some values if formulae in adjacent cells are similar. So your formula might be either:

=COUNTIFS(New!$C$2:$C$354,$A71,New!$J$2:$J$354,"3-Medium",New!$H$2:$H$354, ">20",New!$H$2:$H$354, "<[COLOR=Red][B]=[/B][/COLOR]25")

with the next one:

=COUNTIFS(New!$C$2:$C$354,$A71,New!$J$2:$J$354,"3-Medium",New!$H$2:$H$354, ">25",New!$H$2:$H$354, "<[B][COLOR=Red]=[/COLOR][/B]30")

or move the = sign to the other side:

=COUNTIFS(New!$C$2:$C$354,$A71,New!$J$2:$J$354,"3-Medium",New!$H$2:$H$354, ">[B][COLOR=Red]=[/COLOR][/B]20",New!$H$2:$H$354, "<25")

with the next one:

=COUNTIFS(New!$C$2:$C$354,$A71,New!$J$2:$J$354,"3-Medium",New!$H$2:$H$354, ">[COLOR=Red][B]=[/B][/COLOR]25",New!$H$2:$H$354, "<30")

Now back to the bins; check out the FREQUENCY worksheet function,
or check out:
Tools|Data Analysis…|Histogram
(that's in xl2003, I've not got xl2007 or later in front of me to tell you which part of the ribbon it's on).

 

[p45cal]

I figured out a way. Just added vba to set the precision and all is well - that is till the next challenge.

Range("N:N,G:G,L:L").Select
Range("L1").Activate
Selection.NumberFormat = "m/d/yyyy;@"
Range("N2").Select
ActiveWorkbook.PrecisionAsDisplayed = True

Thank you for making me 'think' and not just give up!

I would advise strongly (unless the workbook is dedicated to just your formulae) against using ActiveWorkbook.PrecisionAsDisplayed = True. Your users won't know this. In addition, the users will have to allow macros to run unless their security levels are set to low.
Instead, get the logic right.

zzzzzs for me now, G'night.

 

[puskacpj]

Thank you for hanging in there with me on this. Still having issues. I did find a duplicate ticket/defect in the aging.

Received: 08/24/2011 0:00
Closed: 09/13/2011 8:48
Age: 20

I use formula for age: (If the ticket is closed, closed - received.
=IF(J105="Closed",P105-G105,TODAY()-G105)

The result is 20 however that ticket is showing twice in the aging. I checked to see the decimals and the Age is actually: 20.367
I assume that is why it fits into more than one age bucket.

HELP!!!!!!!!!!!!! I'm ready to jump off my balcony!

 

[p45cal]

If you use one of the formulae I suggested in msg#5 no age can fit in more than 1 bucket, decimals or not, and no age can be missed altogether, decimals or not.

 

[puskacpj]

I should've done what you said to begin with. Working great now. Thank you sooooo much