COUNTIFS

Try using mapflo for data analysis instead of Excel. Get started for free.

Summary

COUNTIFS formula counts the number of occurrences of value 1 in a data range 1 AND the number of occurrences of value x in data range x.

Syntax

=COUNTIFS(“range 1″,”value 1”, “range 2”, “value 2″….)

In the example below, the formula in cell E2 is =COUNTIFS(A2:A7,”T-shirts”, B2:B7, “L”) which returns the value 1 as “T-shirts” occurs twice in the range A2:A7 but only once when Size is also equal to “L”.

Notes:

• COUNTIFS is not case sensitive.
• Like COUNTIF, COUNTIFS can reference:
  • a number: =COUNTIF(C2:C7,6) returns 1
  • a string: =COUNTIF(A2:A7,”Shorts”) returns 2
  • a cell reference: =COUNTIF(A2:A7,A3) matches to Sandals and returns 2
  • a math operator: =COUNTIF(C2:C7,”>13″) returns 3
  • multiple wild characters: =COUNTIF(A2:A7,”S*”) matches to Sandals and Shorts and therefore returns 4 [where * denotes zero, one or multiple characters]
  • single wild characters: =COUNTIF(A2:A7,”*sh?rts”) matches to T-shirts and Shorts and therefore returns 4 [where ? denotes a single character]

Using mapflo with COUNTIFS

If you like Excel then you’ll love mapflo. mapflo puts data through a flow. It’s easy to add a column that is COUNTIFS using the Rank & Partition Node.  where you partition the data set by in this case Item and Size.

Using the same Rank & Partition Node you can COUNTIF, RANK, SUMIF, AVG, MINIMUM AND MAXIMUM.

No copying down formulas; no worrying about change in dataset size.

Get started with mapflo for free.