Search code examples
excelif-statementsumsumifs

IF Statements with AND and N/A calculations

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)

Here is the picture of the spreadsheet

enter image description here

Solution

  • Hi Thank you to the two guys Tristanto and Brian they really helped get to this answer so the formula that works to solve this puzzle is below. I hope it helps 

    =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))