Issues with formula using data from other sheets

[Claire2606]

Hi I wonder if anyone can hlep me with my problem. I have a spreadsheet that has 5 worksheets.

The first has all details of clients and demographic information
the Second drags through some of the information from the first and is the 'Register' of how many appointments were kept and missed during each month. The third and fourth has the statistcal information from the first two sheets for the first and second halves of the academic sessions by month. the fifth has statistical information per quarter.

My issue on the second sheet is that my formula is returning a #value from information dragged through from the 'Register' Sheet.

=SUMPRODUCT(--(MONTH(Register!$E$13:$E$354)=9),--(Register!$D$13:$D$354="Male"),--(Register!$C$13:$C$354="Year 6"),--(Register!$I$9:$I$350)

The forumla works until i add the last array which then returns the #value.

(Column E - Start date, Column D - Gender, Column C - school year, Column I - number of seesions attended in September)

Any thoughts would be gratefully received.

Thank you

 

[RoryA]

I suspect you have some non-numeric data in column I. To ignore that use this syntax:

=SUMPRODUCT(--(MONTH(Register!$E$13:$E$354)=9),--(Register!$D$13:$D$354="Male"),--(Register!$C$13:$C$354="Year 6"),Register!$I$9:$I$350)

BTW, is your last array supposed to be offset 4 rows up from the others?

 

[Claire2606]

Thank you, that worked brilliantly!

 

[RoryA]

Glad to help.