Explanation
In this example, the goal is to use the COUNTIFS function to count data with “OR logic”. The challenge is the COUNTIFS function applies AND logic by default.
COUNTIFS function
The COUNTIFS function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Conditions are supplied to COUNTIFS in the form of range/criteria pairs — each pair contains one range and the associated criteria for that range:
=COUNTIFS(range1,criteria1)
Count cells in range1 that meet criteria1.
By default, the COUNTIFS function applies AND logic. When you supply multiple conditions, ALL conditions must match in order to generate a count:
=COUNTIFS(range1,criteria1,range1,criteria2)
Count where range1 meets criteria1 AND range1 meets criteria2.
This means if we try to user COUNTIFS like this:
=COUNTIFS(D5:D16,"complete",D5:D16,"pending") // returns 0
The result is zero, since the order status in column D can’t be both “complete” and “pending” at the same time. One solution is to supply multiple criteria in an array constant like this:
=COUNTIFS(D5:D16,{"complete","pending"})
This will cause COUNTIFS to return two results: a count for “complete” and a count for “pending” in array like this:
{6,3}
In the current version of Excel , these results will spill onto the worksheet into two cells. To get a final total in one formula, we nest the COUNTIFS formula inside the SUM function like this:
=SUM(COUNTIFS(D5:D16,{"complete","pending"}))
COUNTIFS returns the counts directly to SUM:
=SUM({6,3}) // returns 9
And the SUM function returns the sum of the array as a final result.
Note: this is an array formula but it doesn’t require special handling in Legacy Excel when using an array constant as above. If you use a cell reference for criteria instead of an array constant, you will need to enter the formula with Control + Shift + Enter in Legacy Excel.
Adding another OR criteria
You can add one additional criteria to this formula, but you’ll need to use a single column array for criteria1 and a single row array for criteria2. So, for example, to count orders that are “Complete” or “Pending”, for “Andy Garcia” or “Bob Jones”, you can use:
=SUM(COUNTIFS(D4:D16,{"complete","pending"},C4:C16,{"Bob Jones";"Andy Garcia"}))
Note we use a comma in the first array constant for a horizontal array and a semicolon for the second array constant for a vertical array. With this configuration, Excel “pairs” elements in the two array constants, and returns counts in a two dimensional array like this
| Bob Jones | Andy Garcia | |
|---|---|---|
| Complete | 1 | 1 |
| Pending | 1 | 0 |
The array result from COUNTIFS is returned directly to the SUM function:
=SUM({1,1;1,0}) // returns 3
And SUM returns the final result, which is 3 in this case.
Note: this technique will only handle two range/criteria pairs. If you have more than two criteria, consider a SUMPRODUCT formula as described here .
Cell reference for criteria
As mentioned above, you can use a cell reference for criteria in an array formula like this:
={SUM(COUNTIFS(range,B1:B2))}
Where range is the criteria range, and B1:B2 is an example cell reference that contains criteria. This formula “just works” in the current version of Excel, which supports dynamic array formulas . However, in Legacy Excel , the formula must be entered with Control + Shift + Enter.
Wildcards and double-counting
COUNTIF and COUNTIFS support wildcards , but you need to be careful not to double-count when you have multiple “contains” conditions with OR logic. See this example for more information
Explanation
Note: later versions of Excel include a native histogram chart , which is easy to create , but not as flexible to format. The example on this page shows one way to create your own histogram data with the FREQUENCY function and use a regular column chart to plot the results. Because FREQUENCY is a formula, the results and chart will dynamically update if data changes.
The FREQUENCY function returns a frequency distribution, which is a summary table that shows the count of each value in a range by “bin”. FREQUENCY is a bit tricky to use, because must be entered as an array formula . On the other hand, once you set up your bins correctly, FREQUENCY will give you all counts at once!
Setup and formula
In the example shown, we have a list of 12 scores in the named range “data” (C5:C16). The range F5:F8 is the named range “bins”. FREQUENCY will treat each bin value as the upper limit for that bin. In other words, each bin will include a count of scores up to and including the bin value. FREQUENCY will also return an “overflow count” – the count of values greater than the last bin.
To enter the FREQUENCY formula, follow these steps in the attached workbook.
Delete existing formulas if needed (see note below).
Select the range G5:G8 (all four cells).
Paste or type this formula in the formula bar:
=FREQUENCY(data,bins)
- Enter the formula as an array formula with control + shift + enter. Excel will automatically add curly braces {}.
Note: To edit existing formulas, you’ll need to select all four cells first, then click Delete. You can select all cells at once with the shortcut control + / .
FREQUENCY will generate a count for each bin and return an array of results like this:
{2;4;5;1;0} // array returned by FREQUENCY
Excel will place the first 4 values in the range G5:G8. Notice the overflow count, zero, is also returned as the fifth element in the array, since there are no values greater than 100. This value is not shown in the worksheet, because we only entered the formula in four cells.
Excel 365
In Excel 365 , you can simply enter one formula in cell G5, and results will automatically spill onto the worksheet. There is no need to use control + shift + enter. However, if you use this approach, you will see the count for the overflow bin output as well. Entering the formula in 4 cells only (as above) suppresses this last value. If you don’t mind the extra value, the single formula option is easier, and you can choose not to plot the value in a chart.
Labels
The labels in E5:E8 are for readability and presentation only. These can be customized as you like. In the example shown, the formula in cell E5, copied down, is:
=IF(F5=MIN(bins),"≤"&F5,F4+1&"-"&F5)
This formula simply builds a label for each bin using the bin values in column F.
Histogram chart
To chart the output from FREQUENCY, follow these steps. First, hold down the control key and select two ranges: E4:E8, and G4:G8. Then insert a column chart (Insert > Charts > Clustered column):
Next, right-click a bar, and format the data series to reduce the gap width to 5% (or as desired):
Change the chart title as you like. In the example shown, we pick up the value in cell B2:
Final chart showing values plotted:
Histogram with Data Analysis ToolPak
Another way to create a histogram in Excel is to use the Data Analysis ToolPak add-in. This is a very simple method, and it works in older versions of Excel. However, one limitation is that the output is static , and won’t update automatically if values in the data change.
