Sumproduct + subtotal + search / +date

[Barbatian]

Hello everyone,

I have a data in 3 columns:
Event NR | Event Type | Date

Event type may be: A,B,C,A+B,A+C

I don't need the result for Event Type A+B but I need them to be separately calculated respectively to A and B Event types:
e.g. A+B Event Should be calculated as 1 Event A and 1 Event B

I have a formula, which allows me to count so but I have to filter the date in other colums to see the result for specific period (e.g. month, year)

=SUMPRODUCT(SUBTOTAL(3;OFFSET(Input!B9:B10193;ROW(Input!B9:B10193)-MIN(ROW(Input!B9:B10193));;1));ISNUMBER(SEARCH("Event A";Input!B9:B10193))+0)

I would like to have a general overview at once for every month/year in e.g. separte sheet.

How Can I modify this formula to also take the date into consideration - I would create a formula for each month/year.
This would also allow me to have an updated overview in case of new "records" for the past.

I also tried using pivot table, however I couldn't manage to separte A+B Events to count separately into A and B Event types.
Pivot table would be perfect if I could somehow separte these calculations.

There is a solution to spread the data source so there is no A+B event but two events instead but because of interpretation we cannot do so.

I would be really thankful for advices and solution.

Kind regards,
Michal​

 

[Andrew Poulsom]

In German you need Januar and Februar rather than January and February. Otherwise month and year won't be coerced into a serial date.