Tuesday, March 10, 2020

COUNTIFS Function in Excel with Example


COUNTIFS ()

This function counts the number of items which match multiple criteria set by the user. 

Best view in desktop mode.


A
B
C
D
1
Item
Date
Price

2
Tyre
12-Feb-20
$                 10.00

3
Brake
12-Feb-20
$                 25.00

4
Seat
12-Feb-20
$               120.00

5
Tyre
13-Feb-20
$                 20.00

6
Seat
15-Feb-20
$               150.00

7
Seat
17-Feb-20
$                 50.00

8
Handle
1-Mar-20
$                 30.00

9
Mirror
1-Mar-20
$               120.00

10
Mirror
5-Mar-20
$                 60.00

11




12




13
Example
Result


14
Seat sales Over $50
2
=COUNTIFS(A2:A10,"Seat",C2:C10,">50")
15
12-Feb Sales Over $100
1
=COUNTIFS(B2:B10,"12-Feb-20",C2:C10,">100")
16
Seat sales after 15-Feb-20
1
=COUNTIFS(A2:A10,"Seat",B2:B10,">15-Feb-20")
17





COUNTIFS can be used to count cells with number, date, and text value that match the criteria.
COUNTIFS Function supports logical operators (>,<,<>,=)

Syntax

=COUNTIFS (range1, criteria1, [range2], [criteria2], ...)

The criteria can be typed in any of the following ways.
To match a specific number type the number, such as =COUNTIFS(C2:C10,30)
To match a piece of text type the text in quotes, such as =COUNTIFS(A2:A10,"Mirror")
To match using operators surround the expression with quotes, such as =COUNTIFS(C2:C10,">50")



Formatting

No special formatting is needed.



Functions for Counting

Ø  To count only numbers, use COUNT()
Ø  To count numbers and text value, use COUNTA()
Ø  To count based on one criteria, use COUNTIF()
Ø  To count based on multiple criteria, COUNTIFS()
Ø  To count blank cells, use COUNTBLANK()





                       




No comments:

Post a Comment