How to count different values which also meet conditions

[jaimegah]

Hi everyone,

I have this problem: I need to count how many different values are there within a column which meet certain conditions. I have the extra difficulty of refering data from a different workbook while it's closed, so I can't use Countifs nor Sumifs, only the Sumproduct workaround. I can't attach a sample file (I don't know why), but it's something like this. Let´s assume I'm a car dealer and I want to know how many different colors I have for cars from year 2015 :

CAR REF YEAR COLOR

001 2015 Red
002 2016 Blue
003 2014 Green
004 2015 Red
005 2015 Blue
006 2016 Red
007 2014 Green

The answer is obviously 2 in this case, but you get the idea. Any help would be really appreciated.

Regards.

[PFDave]

might help:

https://exceljet.net/formula/count-u...-with-criteria

[AliGW]

I can't attach a sample file (I don't know why), ...

I do!

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[jaimegah]

might help:

https://exceljet.net/formula/count-u...-with-criteria

I'm going to try that, thanks!

I do!

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

I understand, thanks!

[shukla.ankur281190]

Row\Col	A	B	C	D	E	F
1	CAR	YEAR	COLOR
2	1	2015	Red
3	2	2016	Blue
4	3	2014	Green
5	4	2015	Red
6	5	2015	Blue
7	6	2016	Red
8	7	2014	Green
9					Formula	{=SUM(--(FREQUENCY(IF(B2:B8>=2015,MATCH(C2:C8,C2:C8,0)),ROW(C2:C8)-ROW(C1))>0))}
10					Result	2

Try

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[jaimegah]

It worked, thanks to you all!! Now I discovered a quite worse difficulty for my task, but I'll open a new thread for that as the forum's rules state.