Excel 2000 - formula
 

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.

--
Richard

Excel 2000 - formula
 

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)

--
Regards

Roger Govier


"Richard" wrote in message
...
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.

--
Richard

Excel 2000 - formula
 

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)
--
Richard


"Roger Govier" wrote:

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)

--
Regards

Roger Govier


"Richard" wrote in message
...
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.

--
Richard