Another SUMPRODUCT giving the incorrect number

[Richard]

I have another question about a SUMPRODUCT I am using.

I am using Windows XP and Excel 2000.

Here is the formula:

=SUMPRODUCT(--(Data!$A$1:$A$10000>=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing
Building Services")*SUM(Data!$E$1:$P$10000))

This formula needs to SUM a total of number in cells E110000 where there
is a 'Yes' in cells D110000 and 'Housing Building Services' is in cells
B1:B10000.

At the moment the formula seems to be doing a SUM of all numbers in
E110000 and then multiplying that number by 2.

The number should be displaying 18 but instead it is displaying 156.

Any idea where I am going wrong.

Again many thanks for any help with this.

 

[Toppers]

Assuming you are summing contents of E$1:$P$10000 where other conditions are
meet:

=SUMPRODUCT(--(Data!$A$1:$A$10000>=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing
Building Services")*(Data!$E$1:$P$10000))

 

[Richard]

Thank you Toppers.

I have amended the formula. The cell is now displaying #VALUE!

I have looked at Help and done a few searches to find out what the problem
could be, but can't see what could be wrong.

Any ideas.

Thanks again.

 

[Richard]

Toppers

I did read on another post that zeros may cause the problem. I do have
zeros in the cells E1:E10000

Hope this might help.

 

[Sloth]

=SUMPRODUCT(--(Data!$A$1:$A$10000>=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing
Building
Services"),Data!$E$1:$E$10000+Data!$F$1:$F$10000+Data!$G$1:$G$10000+Data!$H$1:$H$10000+Data!$I$1:$I$10000+Data!$J$1:$J$10000+Data!$K$1:$K$10000+Data!$L$1:$L$10000+Data!$M$1:$M$10000+Data!$O$1:$O$10000+Data!$P$1:$P$10000)

try the above formula. SUM(Data!$E$1:$P$10000) won't work the way you
describe you want it to. It will simply sum all the numbers and add that
value each time all the cases are true. I am assuming you want to SUM across
for each row, and add that value to the total if all the other cases are
true. IF that's the case, then the above formula should work for you.

 

[Richard]

Thank you Sloth.

You are correct in what I want the formula to do. I used the formula you
have kindly done for me, but the cell is still displaying #VALUE!

Any ideas?

 

[Toppers]

oRichard,
First aplogies for mis-reading your original formula re the
SUM!.

Second: column N is missing from the current formula.

Third: on a sample of 20 rows I got the correct result: the #VALUE error is
likely to be data OR you you have mis-typed a range ... extra or missing
zero... as all ranges must be the same size Blank entries or zeros should not
cause a problem (I have them in my test data)

HTH

 

[Toppers]

this works .....

=SUMPRODUCT(--(Data!$A$1:$A$10000>=DATE(2007,4,1))*(Data!$A$1:$A$10000<=DATE(2008,3,31))*(Data!$D$1:$D$10000="Yes")*(Data!$B$1:$B$10000="Housing
Building Services")*Data!$E$1:$P$10000)

 

[Sloth]

If you have non-numerical data in columns E through P you will have to use
the N() function like this...

=SUMPRODUCT(--(Data!$A$1:$A$10000>=DATE(2007,4,1)),--(Data!$A$1:$A$10000<=DATE(2008,3,31)),--(Data!$D$1:$D$10000="Yes"),--(Data!$B$1:$B$10000="Housing
Building
Services"),N(Data!$E$1:$E$10000)+N(Data!$F$1:$F$10000)+N(Data!$G$1:$G$10000)+N(Data!$H$1:$H$10000)+N(Data!$I$1:$I$10000)+N(Data!$J$1:$J$10000)+N(Data!$K$1:$K$10000)+N(Data!$L$1:$L$10000)+N(Data!$M$1:$M$10000)+N(Data!$N$1:$N$10000)+N(Data!$O$1:$O$10000)+N(Data!$P$1:$P$10000)

It would be easier to use a column to sum E-P across in column Q, and then
put Q1:Q10000 in the SUMPRODUCT formula.

NOTE: Apparently I never learned my ABC's. I skipped column N in my
original formula.

 

[Richard]

Thank you once again Toppers. The formula works great.

I really appreciate your help.

 

[Toppers]

Glad it's working and thanks for the feedback.

Thank you once again Toppers. The formula works great.

I really appreciate your help.

 

[Richard]

Thank you Sloth.

You help is much appreciated.