Counting if particular value for timelength

redpipe67

Board Regular
Joined
Aug 7, 2002
Messages
65
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.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

redpipe67

Board Regular
Joined
Aug 7, 2002
Messages
65
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
 
Last edited:

Rastaman

Board Regular
Joined
Feb 18, 2006
Messages
236
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,110
Messages
5,599,776
Members
414,337
Latest member
ogoodheart

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top