Counting a sample (rows in a column) based on two criteria

[Lija]

Hi guys, here is my question..

Imagine you have two columns: One with values a, b, c or /; and the other with values ranging from 69 to 84 (these are short for years).
I have attached a sheet to make it more clear.

What I need is to count how many values a, b and c I have in each year.

What I have done so far is filtered the sheet, and then used a count function for each combination of (a,b,c) with (69-84).
This works fine, but takes a lot of time, so I wanted to see if there is a shortcut.

Thx

[benishiryo]

hi Lija, welcome to the forum. so what should the answer be? try this:
=SUMPRODUCT((TRIM(B4:B496)<>"/")*(YEAR(C4:C496)>=1969)*(YEAR(C4:C496)<=1984))

[Jonmo1]

You need a pivot table.
Also a 3rd column to extract the year from your dates in column C.
You only had the dates in column C custom formatted to show just yy, but the actual value of the cells is still the whole date.


Workbook29.xlsx

[Lija]

Hi benishiryo,

The answer should be a count of how many a, b and c I have in each year.

For example
3 a in 79
4 a in 80
5 b in 83
18 c in 82

and so on..

[martindwilson]

yep i agree a pivot table is easiest with a 3rd row for year see attached