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

Typr of Car date sold 20 LX 01-Nov-15 16 SL 01-Dec-15 18 SE 01-Jan-16 20 SE 01-Feb-16 18 LX 01-Mar-16 20 LX 01-Apr-16 16 SL 01-May-16 18 SE 01-Jun-16 20 SE 01-Jul-16 18 LX 01-Aug-16 20 SE 01-Sep-16 18 LX 01-Oct-16 20 LX 01-Nov-16 16 SL 01-Dec-16 18 SE 01-Jan-17 16 SL 01-Feb-17 18 SE 01-Mar-17 20 SE 01-Apr-17 <tbody> </tbody>

<tbody>
</tbody>

 

[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.