How to check the presence of a person in Excel

[MAHESA]

I am facing a trouble programming in Excel. I have a formula in a workbook that is located in a page "stats" and 31 sheets in the same workbook representing a file of work. So I wrote a formula allowing me to verify in the 31 pages the presence of a person with this formula

=SUMPRODUCT( NB. IF( INDIRECT ( LINE ( INDIRECT ( "1:31" ) ) & "!$F$15" ) ; "4075" ) )

4075 is the value of a registration number of an agent

My worry is that the Excel will see in the box f15 if 4075 is present and therefore adds a work 1 and so on about 31 sheets in f15. This works great, but the worry is that this agent may be of f15 to f29 or f32 to f40. I can not state in my formula in Excel verifier presence of 4075 of f15 to f29 or f32 to f40 on 31 sheets.

=SUMPRODUCT( NB. IF( INDIRECT ( LINE ( INDIRECT ( "1:31" ) ) & "!$f$15" & "!$f$16" & "!$f$17" ) ; "X" ) )

but its not working?

[absolute55]

I think there is some problem in your formula. Just try to do something like:

=SUMPRODUCT( NB. IF ( INDIRECT ( LINE ( "1:31" ) ) & ( $F$ 15 :$F$ 29 ) * ( $F$ 32 :$F$ 40 ) ; "4075" ) )

[DotNetUser]

I do not want to test an idea:

={SUMPRODUCT(NB.IF(INDIRECT(LINE(INDIRECT( "1:31" ))& "!$F$15:$F$29" ); "4075" ))}

Tests on simple validation and validation matrix (Ctrl + Shift + Enter) if it does not works simply.

[MAHESA]

Thank you and its works great except that I have wrong formula. The formula that I should use is: =SUMPRODUCT(($B15='1'!$B$15:$B$40)*('1'!$F$15:$F$40="X"))+... My worry is that this transaction is calculated on 31 times on and so I made a copy and paste 31 times but made the Excel formula too long that it would have extend much to reduce the formula page 1A31