SUMPRODUCT and other formulas

[Círdan Falassion]

Thanks for the reply, but it's column A (with the formula that calculates
the lapsed days) that I need the solution to, not as you've indicated
Col. B (which is the date). Thanks anyway.

Any other takers...

Thanks

Subject: Re: SUMPRODUCT & other formulas
From: "Andy B" <[email protected]>
Newsgroups: microsoft.public.excel.worksheet.functions

Hi

Have you tried something like:
=SUMPRODUCT((Main!$A$2:$A$401>100)*(Main!$B$2:$B$401<>""))

--
Andy.

Thanks for all the help I've received up to now, it's been
invaluable.

My original question was:
"I have a preset range on my worksheet (A1401) where approximately
a quarter of the sheet is used. Within this spreadsheet I have a
'Lapsed Days'
column and and 'End Date' column (both of which can be empty). I am looking
to create a single cell calculation that tells me the number of
entries whose 'Lapsed Days' are greater than 100 where there isn't a
date (any date)
in the 'End Date' column. I've tried:

=SUMPRODUCT(--(Main!$A$2:$A$401>100),--(NOT(ISNUMBER(Main!$B$2:$B$401)
)))

as kindly suggested by Peo Sjoblom but to no avail (it seems to be
picking up blank/empty cells)."


I've now discovered that the formula in $A$2:$A$401 (one row
example):

=IF(ISBLANK(I2),
"",IF(ISBLANK(O2),DAYS360(I2,TODAY(),TRUE),DAYS360(I2,O2,TRUE)))

which gives me the number of elapsed days between either Start/End
date (if
there is an end date) or Start/Current date, is causing the
SUMPRODUCT formula above to produce incorrect results. This is
because the SUMPRODUCT appears to be picking the 'Lapsed Days'
formula up and including it in its result. I've double checked this
by inserting this test formula temporarily:

=IF([Lapsed Days cell]>100,1,"")

with the result being 1 (confirming my suspicions).

I would now like to know how to achieve the answer to my original question,
whilst counting the occurences of >100 in the 'Lapsed Days' column
(where a
figure actually appears), but discounting the physical formula in the
cell otherwise (if the cell is blank).

Sorry if this is a bit long-winded, but I think that the explanation
requires it.

Thanks

 

[Lady Layla]

Well adapt the formula for the correct column then



: Thanks for the reply, but it's column A (with the formula that calculates
: the lapsed days) that I need the solution to, not as you've indicated
: Col. B (which is the date). Thanks anyway.
:
: Any other takers...
:
: Thanks
:
:
: > Subject: Re: SUMPRODUCT & other formulas
: > From: "Andy B" <[email protected]>
: > Newsgroups: microsoft.public.excel.worksheet.functions
: >
: > Hi
: >
: > Have you tried something like:
: > =SUMPRODUCT((Main!$A$2:$A$401>100)*(Main!$B$2:$B$401<>""))
: >
: > --
: > Andy.
: >
: >
: > : >> Thanks for all the help I've received up to now, it's been
: >> invaluable.
: >>
: >> My original question was:
: >> "I have a preset range on my worksheet (A1401) where approximately
: >> a quarter of the sheet is used. Within this spreadsheet I have a
: >> 'Lapsed
: > Days'
: >> column and and 'End Date' column (both of which can be empty). I am
: > looking
: >> to create a single cell calculation that tells me the number of
: >> entries whose 'Lapsed Days' are greater than 100 where there isn't a
: >> date (any
: > date)
: >> in the 'End Date' column. I've tried:
: >>
: >> =SUMPRODUCT(--(Main!$A$2:$A$401>100),--(NOT(ISNUMBER(Main!$B$2:$B$401)
: >> )))
: >>
: >> as kindly suggested by Peo Sjoblom but to no avail (it seems to be
: >> picking up blank/empty cells)."
: >>
: >>
: >> I've now discovered that the formula in $A$2:$A$401 (one row
: >> example):
: >>
: >> =IF(ISBLANK(I2),
: >> "",IF(ISBLANK(O2),DAYS360(I2,TODAY(),TRUE),DAYS360(I2,O2,TRUE)))
: >>
: >> which gives me the number of elapsed days between either Start/End
: >> date
: > (if
: >> there is an end date) or Start/Current date, is causing the
: >> SUMPRODUCT formula above to produce incorrect results. This is
: >> because the SUMPRODUCT appears to be picking the 'Lapsed Days'
: >> formula up and including it in its result. I've double checked this
: >> by inserting this test formula
: > temporarily:
: >>
: >> =IF([Lapsed Days cell]>100,1,"")
: >>
: >> with the result being 1 (confirming my suspicions).
: >>
: >> I would now like to know how to achieve the answer to my original
: > question,
: >> whilst counting the occurences of >100 in the 'Lapsed Days' column
: >> (where
: > a
: >> figure actually appears), but discounting the physical formula in the
: >> cell otherwise (if the cell is blank).
: >>
: >> Sorry if this is a bit long-winded, but I think that the explanation
: >> requires it.
: >>
: >> Thanks
:
: --
: :: Outgoing mail/postings are checked by AVG 6.0
: :: Virus Database 439, Release Date 04/05/2004