Frequency Formula

Hi John

Thanks for your question!

As you say the Frequency function can tally the number of occurrences of 123, 124, 125 etc. but not for multiple criteria such as 2015,2016.

There are other ways of achieving this but I can't at the moment see how with the Frequncy function. The first one is using the COUNTIFS function and the other is by creating a PivotTable.

1. With COUNTIFS

Suppose you have data as you suggested
Col A Col B
123 2015
123 2015
123 2016
124 2016
124 2015
125 2016
125 2016
125 2015
126 2016
123 2015

With the Countifs function you can create this summary

Col D Col E Col F
..... 2015 2016
123 3 1
124 1 1
125 1 2
126 0 1


The formula in E2 is
=COUNTIFS($A:$A,$D2,$B:$B,E$1)

It counts the number of times 123 AND 2015 occurs together. The $ signs are needed to allow copying of the formula down and across to E2:F5

2. By Pivot Table

First add some column heading eg Figs and Year

Figs Year
123 2015
123 2015
123 2016
124 2016
124 2015
125 2016
125 2016
125 2015
126 2016
123 2015


Select a cell in the data eg A1
Choose Insert, PivotTable, OK
In the field list drag Figs into ROWS labels
Year into COLUMNS
Figs into VALUES
Right click the pivot table, Value Field settings
Choose COUNT


Count of Figs Column Labels
Row Labels 2015 2016 Grand Total
123 3 1 4
124 1 1 2
125 1 2 3
126 1 1
Grand Total 5 5 10

This should give the same summary as with Countifs with added grand totals.

Hope that related to your question and helps for now.

Regards
Doug
STL