I am trying to get a formula that will help count cells that fall within a time window. I came up with a formula but it only works when the time is from AM to PM and not for PM (previous day) to AM (next day). As shown in the image, I want to be able to count the number of Korea trades (from Table 2) within trading window (Table 3). I was trying to use the highlighted formula but apparently it's not working on Table 1. Formula: =COUNTIFS(D13:D16, "Korea", E13:E16, ">="&H4, E13:E16, "<="&I4) Is there a way to achieve this?

CodePudding user response：

When the times cross midnight for Korea, you'll need to do an OR condition rather than an and condition. You could do this by making two countifs functions or use Sumproduct as shown below:

=SUMPRODUCT((D13:D16="Korea")*((E13:E16>=H3) (E13:E16<=I3)))

CodePudding user response：

If you have access to a version of Excel that supports the

Then I created another lambda that would do the overall count.

=LAMBDA(rangeMatch,textMatch,rangeTime,start,end,SUM(MAP(rangeMatch,LAMBDA(x,ISNUMBER(SEARCH(textMatch,x))))*MAP(rangeTime,IsWithin(start,end))))

The function takes 5 parameters:

1. the range for which you are searching for the country name
2. the text to match within the range
3. the range containing the times to evaluate
4. the start time for the valid range (e.g. H3)
5. the end time for the valid range (e.g. I3)

It uses the Map function to evaluate the items in the range using lambdas. The first part of the evaluate creates an array of the items that have the matching text so either TRUE or FALSE. The second part creates an array of items that are within the specified time range - again either TRUE or FALSE. And then it just multiplies those arrays and sums the values essentially getting those where both values are true. Then to use it you just call that named reference:

=CountMatch(D13:D16,"Korea",E13:E16,H4,I4)

CodePudding user response：

You can try this

=SUMPRODUCT(--(LEN(D13:D16)<>LEN(SUBSTITUTE(D13:D16,"Korea","")))*((E13:E16>=H4) (E13:E16<=I4)))