Counting if particular value for timelength

redpipe67

Board Regular
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.

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mae0429

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

redpipe67

Board Regular
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
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,632
Messages
5,838,479
Members
430,549
Latest member
jayjay2022

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.

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

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