Another sumproduct question

[MarianneR]

Hi. I apologize if this has been answered ad nauseum, but I couldn'
seem to find a similar problem.

I have a spreadsheet in which data is entered on a -Master page- an
then data is tallied on another page.

The -Master- (or data entry page) looks something like this:

ID............#........CODE.........DATE
ABCDEF...123.....HOSP123.....10/1/04 (et cetera)

I have two problems. I'm using the sumproduct formula to pull out dat
that matches by ID and then data that matches by code
=sumproduct((Master!$G$2:$G$4000=A2)*(Master!$B$2:$B$4000="HOSP123")
A2 is the ID value on the tally sheet. _My_first_problem_ is that whe
I copy the formula down it only copies the value in the cell. Th
formula looks like it changes, but it doesn't recalculate. I've had t
go into every cell in my -tally- sheet and re-enter A2, A3, etc.
_My_second_problem_ is that if I add new data to the -Master page-, i
doesn't recaculate on the -tally- page.

I've used this formula several times before and haven't had thi
problem. I'm wondering if I should start over. Any help would b
appreciated!

Marianne :cool

 

[Max]

Just one quick thought ..
Check that the calculation mode is set to "Automatic"
(sounds like the calc mode is at "Manual")

Click Tools > Options > Calculation tab
Ensure "Automatic" is checked > OK

 

[Frank Kabel]

Hi
goto 'Tools - Options - Calculate' and check 'Automatic Calculation'

 

[Bob Phillips]

Hi Marianne,

Don't know about the first problem, shouldn't do that (unless you use $A$2),
but on the second, use a dynamic range

=SUMPRODUCT(--(Master!$G$2:OFFSET($G$2,,,COUNTA($G:$G))=A2="a"),--Master!$
B$2:OFFSET($B$2,,,COUNTA($G:$G))="HOSP123"))