Need help with a formula modifier

[Mbutts]

I have a section in this spread sheet that is working really well but now I need to modify it to fit a new requirerment.

The first cell returns the number of personnel
The second cell returns the number of expired certificates
The third cell returns the total number of valid certificates
The forth cell returns the number of blank cells

The core formula that drives this section is based on date. The problem is that I need to enter the text N/A into the column for personnel that this does not apply to. When i do that the formula get messed up because there is somehting else other than a date in the equasion.

What I need is a modifier for the formula that tells excell to ignore any cell that has N/A in the cell. I know it shoud be a simple modification to the core formula but when I try it messes up the formula and I get an error message.

$C$2 is the current date cell.

The formulas that are active for this section are:


number of perosnnel =COUNT(E4:E236)

Total number of certs expired {=SUM((DAYS360(E4:E236, $C$2)>180)*(ISBLANK(E4:E236)=FALSE))} this is the core formula.

Total valid certs =E237-E238

total blank cells =E241-E237

???Any ideas???

 

[Andrew Poulsom]

Try:

=SUM(IF(ISNUMBER(E4:E236),--(DAYS360(E4:E236, $C$2)>180)))

confirmed with Ctrl+Shift+Enter.

 

[Mbutts]

Tested all the variables and it works like a charm. Thank you so much and so quick to respond as well. I do have on question i have not seen the -- used in a formula yet, what does that mean? I am greatful for the answer but I also want to understand what i am reading.

 

[Andrew Poulsom]

The -- coerces TRUE/FALSE into 1/0 to enable summation. Any arithmetic operation can be used, eg +0 or *1, but the double unary minus is the quickest method.

 

[Mbutts]

Okay, thank you for explaining that, i can actually understand what you wrote.