Calculating Values That Do Not Fall Within A Certain Time Range

[excelbytes]

I'm working with values that are entered on various days and at various times. I'm trying to calculate the data to exclude anything that falls between 10:00 am and 12:00 noon. I need to SUMIFS, COUNTIFS, etc., but Excel doesn't allow me to enter two time criteria in the same formula, so what I found myself doing is a formula such as this:

=SUMIFS(TradeProfit,Trades!$D$2:$D$1001,G27,$H$2:$H$1001,"<=10:00:00")+SUMIFS(TradeProfit,Trades!$D$2:$D$1001,G27,$H$2:$H$1001,">11:59:59")

In the above, the range in column "D" are dates, and G27 is a specific date, and the range in column "H" is the entry time.

Is there a way to have both these criteria in the same formula? If not, the above works for summing and counting, but I'm not sure how to accomplish this when I have to find the MIN or MAX in a range excluding that time period.

I know I'm missing something here, but not sure the best approach.

Thanks in advance for your help.

 

[jorismoerings]

Hi,

try something like this:
=SUMPRODUCT(( TradeProfit )*( Trades!$D$2:$D$1001=G27)*(( Trades!$H$2:$H$1001<=TIME(10,0,0))+( Trades!$H$2:$H$1001>TIME(11,59,59))))

and make sure the named range TradeProfit is aligned with the ranges used so cells 2:1001 in the column where tradeprofit is stored

 

[jorismoerings]

to add on previous:

MIN: =MIN(INDEX( (( TradeProfit )*( Trades!$D$2:$D$1001=G27)*(( Trades!$H$2:$H$1001<=TIME(10,0,0))+( Trades!$H$2:$H$1001>TIME(11,59,59)))) ,1))

 

[excelbytes]

to add on previous:

MIN: =MIN(INDEX( (( TradeProfit )*( Trades!$D$2:$D$1001=G27)*(( Trades!$H$2:$H$1001<=TIME(10,0,0))+( Trades!$H$2:$H$1001>TIME(11,59,59)))) ,1))

First, thank you so much for your reply. It's getting me closer.

Two comments:

1) I'm not sure I understand the need for the INDEX function. I modified the formula for MIN as follows and it works fine (it's actually off a slightly different set of values than the SUMIFS example I gave above, plus I'm limiting the range during the testing to watch the process in Evaluate Formula):

=MIN(((( Trades!$AB$2:$AB$20)*( Trades!$C$2:$C$20=P3))*(( Trades!$H$2:$H$20<=TIME(10,0,0))+( Trades!$H$2:$H$20>TIME(11,59,59)))))

2) The problem I have is that I also need the MAX value in the same range, and normally it would work, however when all the values in the range are negative, and Excel goes through the process of multiplying the data range (in this case AB2:AB20) by all the ones and zeros, the zeros are the result of the times I want to remove from the range. And since all the other values are negative, Excel gives me the MAX of zero, even though those values should be excluded from the calculation. Any thoughts on how to address this?

 

[excelbytes]

A modification to my previous post. The following formula suggested:
=MIN(INDEX( (( Trades!$AB$2:$AB$1001)*( Trades!$C$2:$C$1001=$P$3)*(( Trades!$H$2:$H$1001<=TIME(10,0,0))+( Trades!$H$2:$H$1001>TIME(11,59,59)))),1))
actually gives me the the first value in the range that meets the time and date criteria, not the MIN value of those results.

The formula I suggested:
=MIN(((( Trades!$AB$2:$AB$20)*( Trades!$C$2:$C$20=P3))*(( Trades!$H$2:$H$20<=TIME(10,0,0))+( Trades!$H$2:$H$20>TIME(11,59,59)))))
works only when the range of AB has values in it. Currently there is an IF statement that IF conditions aren't met, enter a blank (""). However, this causes a VALUE error. I may need to change that "" to 0.

The issue with the MAX function still exists.

Thanks again for helping.