0

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.

4
  • Hi and welcome to WebApps. Would you please supply some additional information: 1) an example of your data, 2) an example of the output of your original formula, 3) an example of how you perceive the layout of the "mode search between specific date ranges" together with an example of a (hypothetical) successful formula. This information is necessary for us to understand your scenario and you understand the functioning of the formula.
    – Tedinoz
    Apr 30, 2022 at 2:32
  • Thank you for message and sorry for delay. I have tried to recreate in another sheet and even the most basic formulas aren't working correctly. This is the test sheet docs.google.com/spreadsheets/d/… I can't even think why I3 isn't returning a value now let alone my end goal of I5 May 1, 2022 at 10:59
  • 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))) This implies that this is a successful formula, but I can't see it in your spreadsheet. When I apply it to your test data it returns "#N/A", and the arrayformula version returns "#N/A". Would you please clarify whether you have mean that this "returns the mode day" OR "is intended to return the mode day".
    – Tedinoz
    May 2, 2022 at 5:09
  • For some reason that formula is not working since reusing from the original sheet despite the same type of data, so confused with all this. I'm not sure if could could be a formatting issue, expecting numbers on this sheet as opposed to text? May 2, 2022 at 9:25

1 Answer 1

0

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

1
  • Wow, thank you, this is incredible work May 3, 2022 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.