Counting if particular value for timelength

[redpipe67]

Hi everyone,
I am stumped on a particular problem. Right now I have 2 dynamic name ranges "dynvalue" and "dynduration". Dynvalue is comprised of cells containing monetary values from $0.01 and up in no particular order and dynduration is the column next to these monetary values consisting of times in an hh:mm:ss format ranging from 0:00:01 and up, again in no particular order but are relevant to the cell adjacent to it. I am trying to count the number of monetary values for specific time durations.
For example I am trying to get a count of values from $0.01 to $5,000.00 that occured for
1. 5 minutes an under
2. between 0:05:01 and 10 minutes
3. 0:10:01 and over
I can count the number of particular values with
=IF(home="","",COUNTIF(dynvalue, "<$5,000.00"))
This gives me a count of every value under $5,000.00. "home" in the above example is a cell that is either populated or not depending on other things going on. If it is empty the cell this formula is in is blank.

What I can not do is get value counts for the specific time durations mentioned above.
Any help is greatly appreciated.

 

[mae0429]

Can you throw an "AND()" into your criteria?

 

[redpipe67]

mae0429,
Thanks for the reply. I am doing something wrong with AND, I have never used it before and am not getting any results other than #VALUE!.
The following which I was hoping would give me the amount of values under $5,000.00 for the under 5 minute range only gives me 0 which is most deffinatley wrong. Is this because I can not use COUNT to count minutes?

=IF(home="","",COUNTIF(dynvalue, "<$5,000.00")*(dynduration<TIME(0,7,0)))

I'm having issues posting the rest of the cell contents after dynduration...<TIME(0,7,0)))

Thanks

 

[Rastaman]

Have you looked into using SUMPRODUCT? Perhaps something like this will work:

=SUMPRODUCT(--(B2<dynvalue),--(B3<dynduration),--(B4>dynduration))

In cell B2 put the minimum monetary value, in B3 the minimum time, and B4 the maximum time. Alternatively you can use the equals sign to make it less than or equal, etc, as follows:

=SUMPRODUCT(--(B2<=dynvalue),--(B3<=dynduration),--(B4>=dynduration))


Rick