Countifs in two dimensional array, repeating header (non unique)

[emichan]

Hello,

I have a table like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]FY2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FY2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Count?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ticker[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]8/13/14[/TD]
[TD]11/13/14[/TD]
[TD]2/13/14[/TD]
[TD]6/26/14[/TD]
[TD]8/14/15[/TD]
[TD]11/13/15[/TD]
[TD]2/13/15[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]8/9/1514[/TD]
[TD]11/14/14[/TD]
[TD]2/13/14[/TD]
[TD]6/26/14[/TD]
[TD]8/7/15[/TD]
[TD]11/13/15[/TD]
[TD]2/14/15[/TD]
[TD]6/13/15[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]2/12/13[/TD]
[TD]5/10/13[/TD]
[TD]8/9/14[/TD]
[TD]12/13/14[/TD]
[TD]2/10/15[/TD]
[TD]5/9/15[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
I'm trying to count how many inputs i have for particular ticker, for particular quarter, with no regard to year.
That is, I want to get an output that shows for
A, Q1= 2
B, Q4= 2
C, Q3= 1

I tried getting a pivot table, but the problem is i don't have unique header names, so I get Q1, Q2, Q3, Q4 and then Q12, Q22, etc...

Is there a way to get countif which looks up criteria range, criteria, and count range, like sumifs?

Thank you!!

 

[Jonmo1]

Which version of Excel are you using?
For XL2007+, try

=COUNTIFS($B$2:$I$2,"Q1",INDEX($B$2:$I$4,MATCH("A",$A$2:$A$4,0),0),"<>")

 

[mikerickson]

=sumproduct(--(a3:a5="a")*(b2:i2="q4")*(b3:i5<>""))

 

[Jonmo1]

Or based on the cell references for A and Q1

=COUNTIFS($B$2:$I$2,K$2,$B3:$I3,"<>")

 

[emichan]

Thank you both!
I knew it was a sumproduct!!
Thank you!

 

[Jonmo1]

Try the countifs (i did have to adjust the ranges a bit)

Unknown
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		FY2014				FY2015				Count?
2	Ticker	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4
3	A	08/13/2014	11/13/2014	02/13/2014	06/26/2014	08/14/2015	11/13/2015	02/13/2015		2	2	2	1
4	B	8/9/1514	11/14/2014	02/13/2014	06/26/2014	08/07/2015	11/13/2015	02/14/2015	06/13/2015	2	2	2	2
5	C	02/12/2013	05/10/2013	08/09/2014	12/13/2014	02/10/2015	05/09/2015			2	2	1	1
Sheet1

Cell Formulas
Range	Formula
J3	=COUNTIFS($B$2:$I$2,J$2,$B3:$I3,"<>")
J4	=COUNTIFS($B$2:$I$2,J$2,$B4:$I4,"<>")
J5	=COUNTIFS($B$2:$I$2,J$2,$B5:$I5,"<>")
K3	=COUNTIFS($B$2:$I$2,K$2,$B3:$I3,"<>")
K4	=COUNTIFS($B$2:$I$2,K$2,$B4:$I4,"<>")
K5	=COUNTIFS($B$2:$I$2,K$2,$B5:$I5,"<>")
L3	=COUNTIFS($B$2:$I$2,L$2,$B3:$I3,"<>")
L4	=COUNTIFS($B$2:$I$2,L$2,$B4:$I4,"<>")
L5	=COUNTIFS($B$2:$I$2,L$2,$B5:$I5,"<>")
M3	=COUNTIFS($B$2:$I$2,M$2,$B3:$I3,"<>")
M4	=COUNTIFS($B$2:$I$2,M$2,$B4:$I4,"<>")
M5	=COUNTIFS($B$2:$I$2,M$2,$B5:$I5,"<>")