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

This is virtually the same as the thread you posted yesterday, but now you show that some dates may be duplicated and not in the same sequence. Instead of giving us your problem in dribs and drabs, it would be better to give us all of it in the beginning, so that we don't waste our time giving you solutions to partial problems.

Pete