SUM based on conditional formating

[lifeonathread]

Hello!
I'm having trouble with writing a formula properly. I may be using the wrong type of formula to get the result I need.
The basic idea is this:
I'm trying to determine how many shifts were worked on high-incident days for each employee.
The basic format of the sheet is thus:


Column A Date
Column B Opening Dollar Amount
Column C Closing Dollar Amount
Column E Individual Employee Attendance (marked as 1, for attended, blank for not attended)
I currently have conditional formatting applied so that if the value of B or C is greater than 5, or less than -5, then the row is highlighted yellow.
OR($B6<-5,$B6>5,$C6<-5,$C6>5)
This part works great!
However, I need a sum of the attendance marks in E which also fit the criteria of the conditional formatting, since as I understand it, the program can't tell the cell has been colored since I used conditional formatting to do so.
This is the formula I came up with:
=SUMIF($B6:$C40, AND(OR($B6<-5,$B6>5,$C6<-5,$C6>5), $E6>0), E6:E40)
However, the returned value is 0, but I know the correct value should be 9.

In another forum I received the following formula from a kind poster, and plugged it in and it appeared to work.
=SUMPRODUCT(($E$6:$E$36),(($B$6:$B$36<-5)+($B$6:$B$36>5)+($C$6:$C$36<-5)+($C$6:$C$36>5)))
However, after adding a few rows to the sheet and adjusting the ranges so that they corresponded I soon came to realize that what the formula was giving me was NOT the correct answers. It was adding items outside those permitters and in most cases was off by one or two. Perhaps there was something I did wrong in my editing? This is my "corrected" formula based on the new range.
=SUMPRODUCT(($E$9:$E$40),(($B$9:$B$40<-5)+($B$9:$B$40>5)+($C$9:$C$40<-5)+($C$9:$C$40>5)))
However the formula is returning 15 for Jim when a manual count of highlighted incidents is only 13.

Any and all help is greatly appreciated!

COPY OF THE WORKING FILE: https://www.dropbox.com/s/cdoo5epyqu9udsh/WorksheetUPDATED.xlsx?dl=0

 

[Marcelo Branco]

To avoid a double counting you should test if the sum of the conditions is greater than 0 (zero)

So, for Jim, try this
=SUMPRODUCT(($E$9:$E$40),--((($B$9:$B$40<-5)+($B$9:$B$40>5)+($C$9:$C$40<-5)+($C$9:$C$40>5))>0))

Hope this helps

M.

 

[lifeonathread]

To avoid a double counting you should test if the sum of the conditions is greater than 0 (zero)

So, for Jim, try this
=SUMPRODUCT(($E$9:$E$40),--((($B$9:$B$40<-5)+($B$9:$B$40>5)+($C$9:$C$40<-5)+($C$9:$C$40>5))>0))

Hope this helps

M.

That fixed the issue! Thank you so much!!