count formula with row match

[TechRetard]

Looking for the best formula for a count w/ row match

I have a table comprised of:
header: Row 1 comprised of Dates
Rows 2+ are values

Function	2/1/2014	3/1/2014	4/1/2014	5/1/2014
Function A	75%	60%	90%	100%
Function B	75%	60%	90%	100%
Function A	75%	100%	90%	100%
Function C	75%	100%	90%	45%

I want to count the number of times "100%" occurs on my table given a specific date: if date is 3/1/2014, answer = 2

re-working my table is not an option, this is a huge table with thousands of calculations already present

I can do this with an offset but do not think this is the most efficient, also I am want to eventually work in another match...I have 'function' in column 1, so it would be nice to have the formula look at a drop down and count the number of times 100% appears given a specific function

if function = Function C & date = 3/1/2014, answer = 1....if function = "all functions" (note "all functions" is not in any column, it is the whole data set) & date = 3/1/2014, answer = 2

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E
1	Function	2/1/2014	3/1/2014	4/1/2014	5/1/2014
2	Function A	75%	60%	90%	100%
3	Function B	75%	60%	90%	100%
4	Function A	75%	100%	90%	100%
5	Function C	75%	100%	90%	45%
6
7
8	3/1/2014	100%	2

This formula entered in C8:

=COUNTIF(INDEX(B2:E5,0,MATCH(A8,B1:E1,0)),B8)

[Tony Valko]

To add another criteria for a specific function:

Data Range

	A	B	C	D	E
1	Function	2/1/2014	3/1/2014	4/1/2014	5/1/2014
2	Function A	75%	60%	90%	100%
3	Function B	75%	60%	90%	100%
4	Function A	75%	100%	90%	100%
5	Function C	75%	100%	90%	45%
6
7
8	3/1/2014	100%	Function A	1

This formula entered in D8:

=COUNTIFS(INDEX(B2:E5,0,MATCH(A8,B1:E1,0)),B8,A2:A5,C8)

[TechRetard]

Works like a charm ... sumif works as well just because it is 100% (math wouldn't work if it was not so)... but sumifs doesn't work with the function..

I am good to go...thank you

[Tony Valko]

You're welcome. Thanks for the feedback!