Different ways to count values with a formula in an excel report
I always need to count something in my reports, it can be to count 1 or multiple values, to count how many incidents for a specific group, to count only 1 or many conditions are met, etc.
When I use the formula ?
Every time I need to count something.
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) ?
=COUNTIF()
=COUNTIFS()
This formula counts only 1 specific value:
=COUNTIF(B:B,"chocolate")
This formula counts only 2 different values:
=COUNTIFS(B:B,"chocolate",C:C,"dark")
This formula counts only 1 specific letter:
=COUNTIF(B:B,"*d*")
NOTE: if the “d” is at the beginning, put “d*” for instance “donut” and if at the end “*d” for instance “tunod”
This formula counts only 3 different letters:
=COUNTIFS(A:A,"*s*",C:C,"*i*",B:B,"*d*")
This formula counts only numbers for 1 specific condition:
=COUNTIFS(A:A,">1",C:C,"=0",B:B,"chocolate")
This formula counts only the question mark in cell E2:
=COUNTIFS(A:A,"*~?")
You can ask it to count only for a specific condition in cell E3:
=COUNTIFS(A:A,"*~?",B:B,"chocolate")
Interesting Topics
-
Be successfully certified ITIL 4 Managing Professional
Study, study and study, I couldn’t be successfully certified without studying it, if you are interested...
-
Be successfully certified ITIL 4 Strategic Leader
With my ITIL 4 Managing Professional certification (ITIL MP) in the pocket, it was time to go for the...
-
Hide visual and change background color based on selection
Some small tricks to customize the background colour of a text box...
-
Stacked and clustered column chart or double stacked column chart
In excel, I use a lot the combination of clustered and stacked chart...