Formulas behaving different

[wildecoyote1966]

In my workbook I have a couple of issues I'm sure I'm just not seeing it.

ISSUE1: P8 =

Please Login or Register  to view this content.

which is meant to equal O8

Please Login or Register  to view this content.

and N8

Please Login or Register  to view this content.

These are basically the number of days

ISSUE2: Conditional formatting

The conditional formatting at cells B15, I15 and P15 should produce the same results but don't

*I tried to post a cropped screenshot but kept posting whole image so I left it off*


B15 goes all the way to 109, I15 is correct and P15 goes one line more than it should

[AskMeAboutExcel]

For the formula:
N8 is calculating wrong. Try this formula instead
=COUNTIF(B15:B109,">0")+COUNTIF(I15:I109,">0")+COUNTIF(P15:P109,">0")

P8 is missing the first row in the SUM, so should instead be =SUM(P3:P7)

O2 to O7 is miscalculating as some of the data is text instead of a value (e.g. cell Q103)

For the conditional formatting:
try change the formulas to this syntax =AND($P15>0, $P15<=$D$5) - its getting confused if the value in columns B/I/P are 0 or blank, so this formula will capture both

[wildecoyote1966]

@askMeAboutExcel

Thanks that was great.

All I need to do now is get O2:O7 (O8) to match N8 and P8 and it should be almost perfect.

[AskMeAboutExcel]

Found it - Cell O6, change

=COUNTIFS(C15:C109,">0",C15:C109,"<1")+COUNTIFS(J15:J109,">0",J15:J109,"<1")+COUNTIFS(Q15:Q109,"=0",Q15:Q109,"<1")
to
=COUNTIFS(C15:C109,">0",C15:C109,"<1")+COUNTIFS(J15:J109,">0",J15:J109,"<1")+COUNTIFS(Q15:Q109,">0",Q15:Q109,"<1")

[wildecoyote1966]

Thanks @AskMeAboutExcel