Is there a way in Excel to read through 3 columns simultaneously and add the count of specific occurrences in a new column? (Complex, see description)

In addition to this previous question I asked: https://stackoverflow.com/questions/76671196/is-there-a-process-in-excel-that-can-automate-reading-through-a-column-containin, I now have the next larger step in the process.

I have a very large spreadsheet containing over 50,000 rows. They are records of events from a camera trap. Thanks to the solution from the last question I asked here, I now have a new column called "Event Number" which contains incremental numbers based on whether or not a new event was triggered on the camera trap (these are not unique numbers and there are multiple occurrences of each number as there are multiple rows/camera images for each event). But I now have to find the count of certain occurrences within those events. These occurrences are the recorded behaviours of both male and female animals on the camera images. There are a set of 11 behaviours that are all recorded as one word text values.

I started doing this manually in two sheets but realized it will take me a considerable amount of time (there are over 2000 events) as I was really just counting manually and then writing this in the second sheet. Is there any way to automate this using VBA or macro or formulas etc in Excel? Please see the example below:

SHEET 1:

SHEET 2:

I have tried doing this manually as mentioned, but there are over 50,000 rows which contain over 2000 different events, so counting the occurrence of each of these manually is taking a great deal of time. It would be great to understand if there is a way to navigate this faster using the processes in Excel. It is a little bit more complex than just counting for certain text occurrences, as it also must take into consideration the corresponding event number of those rows. Thank you for your help!

Ok, so the very basic principle of COUNTIFS() can be applied here. I'm sure it can be golfed down, but to make it clear:

Formula in H2:

=COUNTIFS(B2:B13,G2:G4,C2:C13,TEXTAFTER(H1:S1," "))*(TEXTBEFORE(C1," ")=TEXTBEFORE(H1:S1," "))+COUNTIFS(B2:B13,G2:G4,D2:D13,TEXTAFTER(H1:S1," "))*(TEXTBEFORE(D1," ")=TEXTBEFORE(H1:S1," "))

This task can be performed using <kbd>POWER QUERY</kbd> as well. The steps are outlined below:

• First convert the source range into a table and name it accordingly, for this example I have named it as AnimalBehavetbl

• Next, open a blank query from <kbd>Data</kbd> Tab --> <kbd>Get & Transform Data</kbd> --> <kbd>Get Data</kbd> --> <kbd>From Other Sources</kbd> --> <kbd>Blank Query</kbd>

• The above lets the Power Query window opens, now from <kbd>Home</kbd> Tab --> <kbd>Advanced Editor</kbd> --> And paste the following <kbd>M-Code</kbd> by removing whatever you see, and press <kbd>Done</kbd>

let
    Source = Excel.CurrentWorkbook(){[Name="AnimalBehavetbl"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"NewEvent", "EventNum"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Behaviour","",Replacer.ReplaceText,{"Attribute"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"Attribute", "Value"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "NewEvent", List.Count)
in
    #"Pivoted Column"

• Lastly, to import it back to Excel --> Click on <kbd>Close & Load</kbd> or <kbd>Close & Load To</kbd> --> The first one which clicked shall create a new sheet with the required output while the latter will prompt a window asking you where to place the result.