IF Statements with AND and N/A calculations

[Conell8383]

I hope you can help. I have a couple of formulas that are breaking due to N/A appearing in cells. I was wondering if these N/A could be accommodated in my formulas to still return a numerical result. In the picture below you can see that in Cell C21 there is 'N/A' and in S21 the is the error #VALUE! The reason for this error is of course because of the 'N/A'
Can the formula in S21 be amended to factor in this 'N/A' and return a number even if it is present. My formula is below

=SUM(AND(C21>=12,C21<=19)*C21,AND(F21>=2,F21<=20)*F21,AND(I21>=2,I21<=20)*I21,AND(L21>=2,L21<=20)*L21)[/FONT][/COLOR]

Here is the picture of the spreadsheet
As always any help is greatly appreciated. Thank you.

 

[AliGW]

Not really! Your formula:

=SUM(AND(C21>=12,C21<=19)*C21,AND(F21>=2,F21<=20)*F21,AND(I21>=2,I21<=20)*I21,AND(L21>=2,L21<=20)*L21)

references cell C21, where there is N/A, so it breaks the AND requirement of the formula. You will need to tweak each of the sections, e.g.
AND(C21>=12,C21<=19)*C21 for Excel to cope with the possibility of N/A.

Maybe this?


IF(C21="N/A",0,AND(C21>=12,C21<=19)*C21)

So your whole formula would have to be:


=SUM(IF(C21="N/A",0,AND(C21>=12,C21<=19)*C21),IF(F21="N/A",0,AND(F21>=2,F21<=20)*F21),IF(I21="N/A",0,AND(I21>=2,I21<=20)*I21),IF(L21="N/A",0,AND(L21>=2,L21<=20)*L21))

 

[Conell8383]

@AliGW: Thank you SO SO SO much this worked amazingly well. Your Excel skills are impressive. Much respect and a 1000 thank yous from Dublin

 

[AliGW]

You're welcome, Conell!