Countif to consider both a single cell and a range of cells at the same time

[rdavid19]

Hi there

I was wondering if there is any chance you could help. I am trying to do Countif on a row but I have to exclude one of the cells in that row as I do not want countif to consider that cell so I am trying to make countif look at the cell before the cell I want to exclude and also at the range that follows the cell I am excluding. I am not sure if this makes sense. Below is how I wrote it but I am getting a "Formula parse error" message. I want it to return a "Major risk" if cell < 1 in all 5 years and so on ....

=IF(COUNTIF(('Financial Analysis Template - Company'!f75,'Financial Analysis Template - Company'!h75:K75),"<1")=5,"Major Risk",

if(COUNTIF(('Financial Analysis Template - Company'!f75,'Financial Analysis Template - Company'!h75:K75),"<1")>=1,"Moderate Risk",

if(COUNTIF(('Financial Analysis Template - Company'!f75,'Financial Analysis Template - Company'!h75:K75),">1")=5,"Minor Risk",

if(countif(('Financial Analysis Template - Charity'!f89,'Financial Analysis Template - Charity'!h89:K89),"<1")=5,"Major Risk",

if(countif(('Financial Analysis Template - Charity'!f89,'Financial Analysis Template - Charity'!h89:K89),"<1")>=1,"Moderate Risk",

if(countif(('Financial Analysis Template - Charity'!f89,'Financial Analysis Template - Charity'!h89:K89),">1")=5,"Minor Risk",

if(countif(('Financial Analysis Template - LLP'!f75,'Financial Analysis Template - LLP'!h75:K75),"<1")=5,"Major Risk",

if(COUNTIF(('Financial Analysis Template - LLP'!f75,'Financial Analysis Template - LLP'!h75:K75),"<1")>=1,"Moderate Risk",

if(COUNTIF(('Financial Analysis Template - LLP'!f75,'Financial Analysis Template - LLP'!h75:K75),">1")=5,"Minor Risk",

if(countif(('Financial Analysis Template - Partnership'!f81,'Financial Analysis Template - Partnership'!h81:K81),"<1")=5,"Major Risk",

if(COUNTIF(('Financial Analysis Template - Partnership'!f81,'Financial Analysis Template - Partnership'!h81:K81),"<1")>=1,"Moderate Risk",

if(COUNTIF(('Financial Analysis Template - Partnership'!f81,'Financial Analysis Template - Partnership'!h81:K81),">1")=5,"Minor Risk",""))))))))))))


Thank you.

[Bob Phillips]

You

=IF((COUNTIF('Financial Analysis Template - Company'!F75:K75,"<1")-('Financial Analysis Template - Company'!G75="<1"))=5,"Major Risk",