Array formula gives wrong answer in one case

[jhallgren]

Using Excel 2000 (yes, it's old - I know), I can't get one variant of array formula to give me right answer and I'm baffled in trying to get proper count based on two criteria.

Here's two ways I've tried it - both give result of 1 when it should be 0:

{=SUM(($G$2:$G$3862=O$3865)*($L$2:$L$3862>$L3887))}
(=COUNT(IF($G$2:$G$3862=O$3865,IF($L$2:$L$3862>$L3887,1)))}

Col G has zip codes and O$3865 has 02661 as criteria #1
Col L has nbr of minutes in HH:MM fmt and $L3887 has 00:20 in proper time fmt as criteria #2

BUT -- There are NO times greater than 20 min for any row that has 02661 zip!

The rest of my array formulas that look for EQUAL match on other various times work just fine using the first formula style so why doesn't the GREATER THAN work?

Also, my overall total for same "over 20 min" gives 61 when it's actually around 27 -- that formula is

{=SUM(IF($L$2:$L$3862>$L$3887,1,0))}

I don't get that either -- I've tried looking at other threads but I've got some visual issues so not all that easy to read huge amts of text.

 

[Sal Paradise]

Try:
=SUMPRODUCT(($G$2:$G$3862=O$3865)*($L$2:$L$3862>$L3887))

(not an array formula)

 

[jhallgren]

Try:
=SUMPRODUCT(($G$2:$G$3862=O$3865)*($L$2:$L$3862>$L3887))

(not an array formula)

Ok - I just did -- but same answer of 1.

BTW, the reason I know answer is wrong is because total counts of time breakdowns is 1 more than a simple COUNTIF of that zip code, plus a custom auto-filter on time using > 20 shows none for that zip.

 

[RoryA]

Perhaps one of your times has a seconds portion, or perhaps you are running into a floating point precision issue.

 

[Richard Schollar]

It's your underlying data. They may not appear to be over 20 minutes, but one of them must be.

 

[Sal Paradise]

Try:
=COUNTIF($L$2:$L$3862,">"&$L$3887)

If that gives anything other than 27, it's a problem with your data.

 

[pgc01]

Ok - I just did -- but same answer of 1.

Analyse the data in the row where the formula counts the 1.

The row is:

=1+MATCH(1,INDEX(($G$2:$G$3862=O$3865)*($L$2:$L$3862>$L3887),0),0)

 

[jhallgren]

Try:
=COUNTIF($L$2:$L$3862,">"&$L$3887)

If that gives anything other than 27, it's a problem with your data.

That DID give me 27! I don't recall seeing that syntax before...what is magic about the ">"& part of that which fixed it?

On pg 410 of "Learn Excel 97-2007 from Mr Excel" (which I have free vers as PDF and has been VERY helpful), I see the ">"& but there it's to concatenate the AVERAGE function on a cell range as criteria so when it's used just with the cell ref itself, what does that do?

And as Richard Schollar said, I now think it may be the data -- as I had a thought while trying to get to sleep...in the subset of rcds that should be counted for zip 02661, there is one row where NO time exists and that cell has text 'cancelled' in it instead -- which is the row that was pointed to via

Analyse the data in the row where the formula counts the 1.

The row is:

=1+MATCH(1,INDEX(($G$2:$G$3862=O$3865)*($L$2:$L$3862>$L3887),0),0)

So what is best way to bypass any non-time value cells and which of the three formula methods that allow me the dual conditions is most suited to that?

I'll also say a BIG THANKS to those who've taken the time to reply thus far! It's greatly appreciated!

 

[RoryA]

=SUMPRODUCT(($G$2:$G$3862=O$3865)*($L$2:$L$3862>$L3887)*ISNUMBER($L$2:$L$3862))

should do it.

 

[jhallgren]

=SUMPRODUCT(($G$2:$G$3862=O$3865)*($L$2:$L$3862>$L3887)*ISNUMBER($L$2:$L$3862))

should do it.

YES! That did it! Got my desired 0! Thanks again!

This forum is once again simply amazing and the members who replied to me are very kind!

Now my only remaining item is:

I don't recall seeing that syntax before...what is magic about the ">"& part of that which fixed it?

Does that force the cell to be evaluated as numeric or what?