# Issues with formula using data from other sheets

#### Claire2606

##### New Member
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

### Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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?

Thank you, that worked brilliantly!

Glad to help.

Replies
5
Views
319
Replies
3
Views
139
Replies
15
Views
571
Replies
11
Views
207
Replies
2
Views
217

Threads
1,203,635
Messages
6,056,464
Members
444,866
Latest member
cr130

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back