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.
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
• 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.
• Formula used in cell B9
=SUMPRODUCT((A9>=$A$3:$A$6)*($A$3:$A$6<=EOMONTH(A9,0))*(B3:F6="ü"))