countifs question

Column A Column B
2012 20
2012 30
2013 50
2013 30
2013 50
2014 10

I've got two columns like this. I need to see if a countifs() would handle it for me, or if there's another formula to use.

I need to get a count of all fields in column B that are unique and are 2013 in Column A. In this case the answer would be 2 (in 2013 there was a 30 and a 50, duplicates are skipped)

try
=SUMPRODUCT((A1:A10=2013)/COUNTIFS(A1:A10,A1:A10&"",B1:B10,B1:B10&""))

Another with CSE..

Formula:

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. Press F2 on that cell and try again.

If the data to be counted really is numbers, try this...


Data Range

	A	B	C	D	E
1	Year	Value	-----	Year	Count
2	2012	20		2013	2
3	2012	30
4	2013	50
5	2013	30
6	2013	50
7	2014	10

This array formula** entered in E2:

=SUM(IF(FREQUENCY(IF(A2:A7=D2,B2:B7),B2:B7),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You're welcome. We appreciate the feedback!