Excel 2000 - formula

[Guest]

I am using Windows XP and Excel 2000. I have 2 worksheets ‘People’ and
‘Data’. In the worksheet ‘People’ I have the code below, which displays a
count total , where the date is between 01/04/2007 and 30/04/2007 in cells in
column A, cells in column C match “A16â€, cells in column D match “Yes†and
cells in column B match “Peopleâ€.

=SUMPRODUCT(--(Data!$A$1:$A$10000>=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2007,4,30)),--(Data!$C$1:$C$10000="A16"),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="People"))

The columns E to P in the ‘Data’ worksheet contain numbers. I need to add
all of these numbers up to give a total but still use the formula above.

eg if E1 contained ‘3’ and F2 contained ‘5’ and H5 contained ‘4’ the amended
formula would display ‘12’ in the cell in the ‘People’ worksheet.

Many thanks in advance.

 

[Roger Govier]

Hi Richard

Just add *$E$1:$P$1000 to the end of your formula

=SUMPRODUCT(
--(Data!$A$1:$A$10000>=DATE(2007,4,1)),
--(Data!$A$1:$A$10000<=DATE(2007,4,30)),
--(Data!$C$1:$C$10000="A16"),
--(Data!$D$1:$D$10000="Yes"),
--(Data!$B$1:$B$10000="People")*
$E$1:$P$1000)

 

[Guest]

Thanks you Roger, I tweaked your code and and it now works. I used this on
the end of the formula:

*SUM(Data!$E$1:$P$10000)