Count this and not that when this is "this"

[VIGILANCE]

Hi,

Thank you very much for your time.

I want to count the number of issues and leave out the "break" where the counted cells are the date I want.

Date # Issue Status Resolution Type Incident # TIME STAMP

10/3/2017 16 something I don't know what R As long as it works C 2803356 4:25:41 PM
10/3/2017 15 BREAK 3:45 BREAK B 3:45:27 PM
10/3/2017 14 BREAK 3:30 BREAK B 3:30:11 PM
10/3/2017 13 something I don't know what R As long as it works EM 280321 3:28:49 PM
10/3/2017 12 something I don't know what R As long as it works C 280329 3:24:51 PM
10/2/2017 5 something I don't know what R As long as it works C 280183 10:22:46 PM
10/2/2017 4 BREAK 8:54 BREAK B 8:54:34 PM
10/2/2017 3 BREAK 8:39 BREAK B 8:39:11 PM
10/2/2017 2 something I don't know what R As long as it works Old 279464 8:36:04 PM
10/2/2017 1 something I don't know what R As long as it works VM 280171 8:24:25 PM
10/2/2017 3 something I don't know what R As long as it works VM 280163 8:23:14 PM

Been trying on my own and couldn't figure this out, please help. Thanks!

 

[GingerStepChild]

If I read you correctly you want to count the rows that don't have BREAK.

I'm not an expert by any means but would do this.

In new column (here I use column I ) add =IF(OR(C3="BREAK", E3="BREAK"),0,1) to each row. When the formula sees a "BREAK" it will return 0 else it will return a 1

At the end add in a new cell =SUM(I3:I13) to add up all the 1s.

 

[VIGILANCE]

If I read you correctly you want to count the rows that don't have BREAK.

I'm not an expert by any means but would do this.

In new column (here I use column I ) add =IF(OR(C3="BREAK", E3="BREAK"),0,1) to each row. When the formula sees a "BREAK" it will return 0 else it will return a 1

At the end add in a new cell =SUM(I3:I13) to add up all the 1s.

Thanks for your reply. You are right, however the secondary condition is that the cells counted must be of a specific date. In the data that I miserably failed to post understandably, there are two dates listed.

 

[GingerStepChild]

That's Ok.

In Cell I1 put in the date you want to analyse, then put this into every row as before =IF(A3=$I$1,IF(OR(C3="BREAK",E3="BREAK"),0,1),0)

If the date in A3 equals the date in I1 then check for the BREAK condition else return a 0.

Doing it this way means you just have to put in the date and it will calculate for you. Maybe put the sum in I2 for convenience.

 

[steve the fish]

You could use a COUNTIFS. Something like:

=COUNTIFS(A3:A13,A3,C3:C13,"<>BREAK")

where in this example A3 contains your date of interest.