itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Count a specific value within a month with a formula in an excel report

A pivot table is more practical to count how many times a value appears within a month but sometimes, I need to know in a quick way so using a formula is much better. For instance, I want to know how many times chocolate appears only in February.

formula excel

 

When I use the formula ?

To know quickly how many times a value appears in a month.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How is/are the formula(s) ?

=COUNTIFS()

=COUNTIFS(A:A,">="&H1,A:A,"<="&H2,B:B,"chocolate") or =COUNTIFS(A:A,">="&H1,A:A,"<="&H2,B:B,D1)

If I don’t want to use the cells of start/end month, I have to put the date into the formula, in this case, I have 2 options:

  • First option:
    =COUNTIFS(A:A,">=01.02.2019",A:A,"<=28.02.2019 23:59",B:B,"chocolate") or =COUNTIFS(A:A,">=01.02.2019",A:A,"<=28.02.2019 23:59",B:B,D1)
  • Second option:
    =COUNTIFS(A:A,">="&DATE(YEAR(NOW()),MONTH(NOW())-1,1),A:A,"<"&DATE(YEAR(NOW()),MONTH(NOW()),1),B:B,"chocolate") or =COUNTIFS(A:A,">="&DATE(YEAR(NOW()),MONTH(NOW())-1,1),A:A,"<"&DATE(YEAR(NOW()),MONTH(NOW()),1),B:B,D1)

Interesting Topics