Count number of text values in a specific row

[Guest]

Hello I have a data sheet that looks likes this:

Reading Date Site 1 Site 2 Site 3 Site 4 .....
Jun 1,2007 XP XP D XP
Jun 2, 2007 XP D D P

Question:
I want use a function that will enable the user to enter a date, and the
function will search for that date, and count the number of times the code
XP, is entered.

Can anyone help me out with my quest?

 

[Guest]

Count the number of "XP" in that row where the date is? Okay, assume that the
dates are in column A, and the site columns are B, C, D, E...

I would use this:

=COUNTIF(INDEX($A$100:$E$100, MATCH(date, $A:$A, 0), 1):INDEX($A$100:$E$100,
MATCH(date, $A:$A, 0), 5), "=XP")

Replace "$A$100:$E$100" with the range that contains your data... and
replace "5" in the MATCH function to the number of columns in your data range.

"date" is the reference that contains the date you want to look up.

Hope that helps.

 

[T. Valko]

Try this:

H1 = some date

=COUNTIF(INDEX(A1:E3,MATCH(H1,A1:A3,0),),"xp")

Biff

 

[Guest]

Assume date in A5:A100, Site 1,2,3,4.... in B5:Z100
A1: is the user to enter date

B1: =SUMPRODUCT((A5:A100=A1)*(B5:Z100="XP"))