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

[emichan]

Hello,

I have a table like this:

FY2014

FY2015

Count?

Ticker

Q1

Q2

Q3

Q4

Q1

Q2

Q3

Q4

Q1

Q2

Q3

Q4

A

8/13/14

11/13/14

2/13/14

6/26/14

8/14/15

11/13/15

2/13/15

2

2

2

1

B

8/9/1514

11/14/14

2/13/14

6/26/14

8/7/15

11/13/15

2/14/15

6/13/15

2

2

2

2

C

2/12/13

5/10/13

8/9/14

12/13/14

2/10/15

5/9/15

2

2

1

1

<tbody>
</tbody>

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,"<>")