Dynamic row in function

[papaexcel]

I have the following formula in Sheet1 B3:

=COUNTIF(Sheet2!B2:F2,Sheet1!A1)

What I would like is for the Sheet2!B2:F2 reference to be dynamic. I want the formula in cell B3 to go and count all the occurences of what is entered in cell A1, but only for the row with the heading 'boat' (or whatever is listed beside the cell).

I hope that makes sense. The spreadsheet is attached for your reference.

Thanks a ton!

[martindwilson]

nope! what are you actually counting? showing an example that only results in 1 isnt very clear

[papaexcel]

I'm counting the number of occurences of whatever is in cell A1 for .

I've updated the spreadsheet. Hopefully it's a little more understandable.

Thanks again!

[NBVC]

try in B2:

=SUMPRODUCT((Sheet2!$A$1:$A$3=$A2)*(Sheet2!$B$1:$F$3=$A$1))

copied down.

[papaexcel]

Works great. Thank you.