Excel formula problem SUMPRODUCT

[ahzulu]

Hi Guys,

I have a problem with a formula adding up rows against 2 parameters.

I am trying to add up rows on a report that 1) show that the actio
against it is "open" and its target completion date is overdue.

I have tried the following formula but it will not work.

=SUMPRODUCT(--(H5:H47="OPEN")*(G5:G47="<TODAY()))

This formula works if I substitute the date part with another tex
parameter but will have nothing to do with dates even if I put in
specific date that I know matches one from the column, I still get 0

Any ideas.

Thanks in advance

And

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(H5:H47="OPEN"),--(G5:G47<TODAY()))

 

[Bob Phillips]

Also, if you want some date other than today to test against, put that date
in a cell and compare against the cell

=SUMPRODUCT((H5:H47="OPEN")*(G5:G47<=C1))

or embed in the formula like so

=SUMPRODUCT((H5:H47="OPEN")*(G5:G47<="01-Apr-2004"*1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi
try
=SUMPRODUCT(--(H5:H47="OPEN"),--(G5:G47<TODAY()))

 

[ahzulu]

Thank you Frank

It works fine and thanks once again for such a speedy response

Regards,


And

 

[ahzulu]

Thanks for that Bob,

You pre-empted one of my next 2 questions!

The second one being, instead of posting against a single date, ho
would I do it against for eg all "open" rows in april. I've tried
=SUMPRODUCT(--(g5:g51="open"),--(H5:H51>F1<F2)) where F1 = 01/04/04 an
F2= 30/04/04 but get no result.

Thanking you in advance

Regards,

And

 

[Bob Phillips]

I'm well known as a mind-reader (not<G>).

=SUMPRODUCT((g5:g51="open"),*(H5:H51>F1)*(H5:H51<F2))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[ahzulu]

Thats great.

Thanks for your speedy reply

Regards,

And

 

[twaccess]

Please could someone run that by me again..

I mean the differences between using -- or * in the middle of
sumproduct formulas.

I have read a couple of posts tonight where people's sumproduct
formulas have been partially solved by the use of -- instead of * as in
this example here.

It would be nice to be able to understand why.

 

[Bob Phillips]

IF the result is the product of two conditions being multiplied, it is fine
to multiply them together as this will coerce the True/False values to 1/0
to allow the summing

=SUMPRODUCT((condition1)*(condition2)).

However, if there is only one condition, you can coerce to 1/0 with the
double unary --

=SUMPRODUCT(--(condition1))

You could achieve this equally as well with

=SUMPRODUCT((1*(condition1)))

and equally the first could be represented as

=SUMPRODUCT(--(condition1),--(condition2)).

There is no situation whereby a solution using -- could not be achieved
somehow with a * (cue flood of responses to prove otherwise<vbg>). However,
the best rationale for suing --is in this statement from Harlan Grove ...

.... Now, as I've written before, it's not the speed of double unary minuses
I like, it's the fact that due to Excel's operator precedence it's harder to
screw up double unary minuses with typos than it is to screw up the
alternatives ^1, *1, +0. Also, since I read left to right, I prefer my
number type coercions on the left rather than the right of my Boolean
expressions, and -- looks nicer than 1* or 0+. Wrapping Boolean expressions
inside N() is another alternative, possibly clearer, but it eats a nested
function call level, so I don't use it.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)