Could multiple criteria based on year from another column

[bobbych]

Hi all

I need a formula that will count how many 16 SL and 20 SE cars were sold in 2016. just 1 figure adding the 2 cars together. Thanks in advance

[TABLE="width: 100"]
<tbody>[TR]
[TD][TABLE="width: 300"]
<tbody>[TR]
[TD="width: 98"]Typr of Car[/TD]
[TD="width: 70"]date sold[/TD]
[/TR]
[TR]
[TD]20 LX[/TD]
[TD="align: right"]01-Nov-15[/TD]
[/TR]
[TR]
[TD]16 SL[/TD]
[TD="align: right"]01-Dec-15[/TD]
[/TR]
[TR]
[TD]18 SE[/TD]
[TD="align: right"]01-Jan-16[/TD]
[/TR]
[TR]
[TD]20 SE[/TD]
[TD="align: right"]01-Feb-16[/TD]
[/TR]
[TR]
[TD]18 LX[/TD]
[TD="align: right"]01-Mar-16[/TD]
[/TR]
[TR]
[TD]20 LX[/TD]
[TD="align: right"]01-Apr-16[/TD]
[/TR]
[TR]
[TD]16 SL[/TD]
[TD="align: right"]01-May-16[/TD]
[/TR]
[TR]
[TD]18 SE[/TD]
[TD="align: right"]01-Jun-16[/TD]
[/TR]
[TR]
[TD]20 SE[/TD]
[TD="align: right"]01-Jul-16[/TD]
[/TR]
[TR]
[TD]18 LX[/TD]
[TD="align: right"]01-Aug-16[/TD]
[/TR]
[TR]
[TD]20 SE[/TD]
[TD="align: right"]01-Sep-16[/TD]
[/TR]
[TR]
[TD]18 LX[/TD]
[TD="align: right"]01-Oct-16[/TD]
[/TR]
[TR]
[TD]20 LX[/TD]
[TD="align: right"]01-Nov-16[/TD]
[/TR]
[TR]
[TD]16 SL[/TD]
[TD="align: right"]01-Dec-16[/TD]
[/TR]
[TR]
[TD]18 SE[/TD]
[TD="align: right"]01-Jan-17[/TD]
[/TR]
[TR]
[TD]16 SL[/TD]
[TD="align: right"]01-Feb-17[/TD]
[/TR]
[TR]
[TD]18 SE[/TD]
[TD="align: right"]01-Mar-17[/TD]
[/TR]
[TR]
[TD]20 SE[/TD]
[TD="align: right"]01-Apr-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MrKowz]

I'm sure there is a better way to do this, but I was able to come up with:

Excel 2013/2016
	A	B	C	D
1	Typr of Car	date sold		Result
2	20 LX	1-Nov-15		5
3	16 SL	1-Dec-15
4	18 SE	1-Jan-16
5	20 SE	1-Feb-16
6	18 LX	1-Mar-16
7	20 LX	1-Apr-16
8	16 SL	1-May-16
9	18 SE	1-Jun-16
10	20 SE	1-Jul-16
11	18 LX	1-Aug-16
12	20 SE	1-Sep-16
13	18 LX	1-Oct-16
14	20 LX	1-Nov-16
15	16 SL	1-Dec-16
16	18 SE	1-Jan-17
17	16 SL	1-Feb-17
18	18 SE	1-Mar-17
19	20 SE	1-Apr-17
Sheet2

Cell Formulas
Range	Formula
D2	{=SUM(IF(A2:A19="16 SL",1,IF(A2:A19="20 SE",1,0))*IF(YEAR(B2:B19)=2016,1,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.