Count number of times a specific text appears for a certain date

Hi,

Following on from a similar post I made yesterday. What I am looking to do is count the number of times a specific value is found for a given date, where the specific date can be found within different rows of the column.

Unless you guys come up with a totally better method, for now as shown in the below screenshot (full spreadsheet also attached). I created a separate table on the right starting from column G, where I count the number of times each event (Major, Minor or blank) occurs for a given date, so 11/11/2019 as shown within cell G2 and G3.

I know I can do a COUNTIF but I don't know how to do the formula for example to count the number of times the text "Major occurs for all rows which are listed as date "11/11/2019". As shown in my example below, "Major" occurs 4 times across the 2 columns which are dated as 11/11/2019. I also want the formula to count the total of blank cells and not just ignore them.

I have manually entered for now the first 4 rows on the right table to show how the results should be populated based on the table on the left. I want to enter formulas within cells H2 to J14 (providing this is the method to do so haha!) to count the total for me.

Capture3.PNG


- Is there a certain function(s) that works best?
- Is there a neater way to go about this then the table I created?
- Also for the blank cells, is whatever formula you guys suggest going to be able to pick the blank cells up and count it to the total?

I would appreciate if someone could help me please and happy to clarify anything if I am unclear