match multiple criteria in both rows and columns

[jbo]

I have looked on these threads for a while now and have come up with 2
different formulas as a result to get what I need. However, both formulas
give me the #NUM! error. I have a number of timesheets that are broken out
by area, by quarter, and by day. I have a summary spreadsheet that I am
trying to sum all of the hours by week and by each area and each quarter.
Following are the 2 formulas I have constructed to serve this purpose. Can
anyone tell me what I am doing wrong?

=SUMPRODUCT((Booth!A:A='NU
Schedule'!A1)*(Booth!B:B=1)*(Booth!D="Total")*(Booth!2:2>='NU
Schedule'!C68)*(Booth!2:2<'NU Schedule'!C69)*(Booth!I3:IV65000))

=INDEX(Booth!$1:$65536,MATCH(1,(Booth!A:A='NU
Schedule'!$A$1)*(Booth!B:B=1)*(Booth!D="Total"),0),17)

I have already tried pressing ctrl+shift+enter for the array, but this has
not worked.
Also, when I put these formulas into one cell, all of the other cells with
formulas pulling data from that worksheet that worked previously all of a
sudden say #N/A. Can someone tell me why that is also?

Thanks!

 

[Shane Devenshire]

Hi

=SUMPRODUCT((Booth!A:A='NU
Schedule'!A1)*(Booth!B:B=1)*(Booth!D="Total")*(Booth!2:2>='NU
Schedule'!C68)*(Booth!2:2<'NU Schedule'!C69)*(Booth!I3:IV65000))

1. Can't use B:B references in 2003 or earlier
2. You range reference must all be the same length - you can't multiply
B:B*I3:IV65000 they don't have the same number of rows.

There may be other problems, but this should get you started.

 

[jbo]

That is very helpful. The information about B:B references will help me with
the other formula. Thanks!