Sumproduct. Once more please...

[Llyllyll]

Afternoon,

An example of my spreadsheet (Col A - J) below (hope it displays OK!):

ID Case Reference Name F/A Team SC Start Days End
047 Comp01 001/TD181C Smiths F Y N 10/02/04 104
054 Comp02 002/TD693N Smiths A N N 15/04/04 27 20/04/04
055 Comp03 003/TD4315 Smiths A N N 12/04/04 271
056 Comp04 004/TD2544 Smiths F N N 07/04/04 290
057 Comp05 005/TD275N Smiths A N N 26/04/04 411

I am looking to be able to count the number of entries I have where
'Days'(Col I) is greater than 100 and where there is no entry in 'End'(Col
J). I have tried many variations on sumproduct and thought that my current
effort:

=SUMPRODUCT(--(N(Main!I2:I401>100)),--(NOT(ISNUMBER(Main!J2:J401))))

would solve my problems. However it seems that even with the N is the
formula above, the formula that I have in Col I (a totally separate
function, example below):

=IF(ISBLANK(H1),
"",IF(ISBLANK(J1),DAYS360(H1,TODAY(),TRUE),DAYS360(H1,J1,TRUE)))

is still being counted by the first part of the sumproduct formula.

Your comments and help would be much appreciated.

Thanks

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(Main!I2:I401>100),--(Main!J2:J401=""))

 

[Círdan Falassion]

Hi
try
=SUMPRODUCT(--(Main!I2:I401>100),--(Main!J2:J401=""))

Thanks Frank, but it's this first bit:

(--(Main!I2:I401>100)

that's causing the problem. This works if I remove the:

=IF(ISBLANK(H1) etc.

formula, but it's needed for other calculations. Basically I think what
I'm after is a way to count the cells where the entry is >100 while
ignoring the formula in that cell. Is this possible?

 

[Aladin Akyurek]

One of:

=SUMPRODUCT(--ISNUMBER(Main!I2:I401),--(Main!I2:I401>100),--(Main!J2:J401=""
))

=SUMPRODUCT(--ISNUMBER(Main!I2:I401),--(Main!I2:I401>100),--ISBLANK(Main!J2:
J401))