#VALUE! error with if/countif/sum functions

[EMcK01]

Hi,

hopefully this is straightforward but I can't think of a way round it.

In cell E21 I have the following formula: =IF(COUNTIF($C21,"*")+(COUNTIF(D21,"*"))=2,IF(D21=$C$21,5,0),"")
essentially looking for a return value of either 5 or 0 as long as there is text in cells C21 and D21

my issue is this causes an error in cell E23 which has the following formula: =IF(SUM(E12:E15,E17:E19)=35,45,IF(SUM(E12:E15)=20,25+SUM(E17:E19),IF(SUM(E17:E19)=15,20+SUM(E12:E15),SUM(E12:E15,E17:E19))))+E21

If I change the formula in E21 to IF(D21=$C$21,5,0), the error clears however by removing the COUNTIF it allows a 5 value to be returned when it shouldn't.

Any help would be appreciated.

Thanks,
EMcK

 

[mart37]

=if(countif($c21,"*")+(countif(d21,"*"))=2,if(d21=$c$21,5,0),0)

 

[FDibbins]

Maybe this?
=if(and(counta($C21:D21)=2,D21=$C$21),5,"")
However, you need to include E21 in the SUM formula, otherwise it will error trying to add "" so maybe something like this?
=sum(IF(SUM(E12:E15,E17:E19)=35,45,IF(SUM(E12:E15)=20,25+SUM(E17:E19),IF(SUM(E17:E19)=15,20+SUM(E12:E15),SUM(E12:E15,E17:E19 )))),E21)

Im pretty sure that last formula can be simplified, but try that for no

 

[EMcK01]

Mart37, thanks for the response, that solved the issue I was having.

Ford, I should have mentioned that the formula in E21 could appear in G21 or L21 say, so the range that you used wouldn't work.

Thanks,
EMcK