Search code examples
excelexcel-formulacountcountif

How to count occurrences main category wise for a month in Excel

As shown in below image, need to count tick marks month wise and main category wise. For example for April month there are total 7 ticks in main category and 7 tics in main category_1. How to formulate it.

enter image description here

I want to count the tick mark for a month(Apr-23,Jun-23, Sep-23 ) by category(Main Category, Main Category_1) by formulation.

Solution

  • Try using SUMPRODUCT() or SUM() if using MS365

    enter image description here

    • Formula used in cell B9

    =SUMPRODUCT((B3:F6="ü")*(TEXT(A3:A6,"mmm-yy")="Apr-23"))

    Notes: Change ü to P and months are actually dates, so it needs to converted to text format, like shown in formula.

    Look at this example below, i have used a cell reference to make it dynamic, here the cell A9 is an actual date formatted as mmm-yy and used the following formula.

    enter image description here

    • Formula used in cell B9

    =SUMPRODUCT((A9>=$A$3:$A$6)*($A$3:$A$6<=EOMONTH(A9,0))*(B3:F6="ü"))