Need column mode after query/filtering huge data set with multiples ifs and ranges

Question

I created this which returns the mode day in text of all the data that has been received in every transaction

=index($B$2:$B$400,mode(match($B$2:$B407,$B$2:$B407,0)))

I now need to find a way to confine this mode search between specific date ranges A2:A,">=2022/04/01", A2:A,"<=2022/04/30" and a specific value in column H $H$2:$H,"BnD".

I have tried so many different ways of connecting different formulas which mostly work individually and never all together.

I tried this
=COUNTIFS(A2:A,">=2022/04/01", A2:A,"<=2022/04/30",B2:B,"Saturday", $H$2:$H,"BnD")+COUNTIFS(A2:A,">=01/04/2022", A2:A,"<=30/04/2022",B2:B,"Saturday", $H$2:$H,"BnD")
which provides me what I require minus the mode because I have specified a day in this one but in need the mode of that column B.

I am now trying to break down the individual parts and build them back up. Falling at 3rd hurdle! =countifs(A2:A,">=1/4/2022",A2:A,"<=30/4/2022") Works correctly and =countif(H2:H,"BnD")
works, but when combined
=countifs(A6:A,">=23/12/2021",A6:A,"<=24/12/2021",H2:H,"BnD")
I get value error even though in this new range all rows have values in the required cells. I think this may be preventing from getting anywhere with this.

Example sheet https://docs.google.com/spreadsheets/d/1anfWAkxa73-N7TydeMXVRNTuA1UB-vJ9A3iEnDjXa-I/edit?usp=sharing loosely has the same setup but the most basic formulas aren't even working on this one either.

I used #N/A in occasionally as this appears in the live sheet when a partner is unknown or doesn't have a value at all.

Any suggestions welcome.

Answer 1

Counts "Partner 1" in F

• =countif(F2:F,"Partner 1")
• Result: 10

Counts all rows within a date range from A2:A

• Hardcoded date

  • =countifs($A$2:$A,">="&date(2020,4,1),$A$2:$A,"<="&date(2021,1,30))
  • Result: 28

• Dynamic date

  • Start Date: Cell I7 = 1 Apr 2020
  • End date: Cell I8 = 30 Jan 2021
  • =countifs($A$2:$A,">="&$I$7,$A$2:$A,"<="&$I$8)
  • Result: 28

Returns mode of all days in B2:B

• =index(B2:B, match(max(countif(B2:B, B2:B)), countif(B2:B, B2:B), 0))
• Result: Thursday

Returns mode day B2:B if A2:A is >=01/01/2020 <=01/04/2022, C2:C >= 08.00 <=17:00, F2:F = "Partner 1"

• Formula to generate list of days

  • =query({A1:F201},"select Col2 where Col6 contains 'Partner 1' and Col1 >= datetime '2020-01-01 08:00:00' and Col1 <= datetime '2022-04-01 17:00:00' label Col2 ''")
  • Result: 100 records

• Formula to return mode of days

  • simply substitute the "list of days" formula in each range of the Return Mode of Days formula.
  • =index(query({A1:F201},"select Col2 where Col6 contains 'Partner 1' and Col1 >= datetime '2020-01-01 08:00:00' and Col1 <= datetime '2022-04-01 17:00:00' label Col2 ''"), match(max(countif(query({A1:F201},"select Col2 where Col6 contains 'Partner 1' and Col1 >= datetime '2020-01-01 08:00:00' and Col1 <= datetime '2022-04-01 17:00:00' label Col2 ''"), query({A1:F201},"select Col2 where Col6 contains 'Partner 1' and Col1 >= datetime '2020-01-01 08:00:00' and Col1 <= datetime '2022-04-01 17:00:00' label Col2 ''"))), countif(query({A1:F201},"select Col2 where Col6 contains 'Partner 1' and Col1 >= datetime '2020-01-01 08:00:00' and Col1 <= datetime '2022-04-01 17:00:00' label Col2 ''"), query({A1:F201},"select Col2 where Col6 contains 'Partner 1' and Col1 >= datetime '2020-01-01 08:00:00' and Col1 <= datetime '2022-04-01 17:00:00' label Col2 ''")), 0))

• Result: Friday