It could be SUMPRODUCT?

[stupidquestion]

Hello everyone! This problem has made me need to register, I can't seem to get my head around it!

I'm working with someone else's spreadsheet, and it's been giving me some headaches. The sheet is used to help us manage our resources whilst we're implementing a proper tool for this, and my 'old' sheet was deemed 'too simple' to use… so here I am.

Mock up of the sheet is below for context - I know you all understand cell references, it's more to make sure I don't mess up:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name[/TD]
[TD]Bench
[/TD]
[TD]8-Aug[/TD]
[TD]9-Aug[/TD]
[TD]10-Aug[/TD]
[TD]11-Aug[/TD]
[TD]12-Aug[/TD]
[TD]13-Aug[/TD]
[TD]14-Aug[/TD]
[TD]15-Aug[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Mickey Mouse[/TD]
[TD]=(COUNTA(C2:J2)-(COUNTIF(C2:J2,">0")))[/TD]
[TD]=SUMIFS('Raw Data'!Q:Q,'Raw Data'!$E:$E,'Bench Report'!$A2)[/TD]
[TD]=SUMIFS('Raw Data'!P:P,'Raw Data'!$E:$E,'Bench Report'!$A2)[/TD]
[TD]=SUMIFS('Raw Data'!Q:Q,'Raw Data'!$E:$E,'Bench Report'!$A2)[/TD]
[TD][/TD]
[TD][/TD]
[TD]=SUMIFS('Raw Data'!T:T,'Raw Data'!$E:$E,'Bench Report'!$A2)[/TD]
[TD]=SUMIFS('Raw Data'!U:U,'Raw Data'!$E:$E,'Bench Report'!$A2)[/TD]
[TD]=SUMIFS('Raw Data'!V:V,'Raw Data'!$E:$E,'Bench Report'!$A2)[/TD]
[/TR]
</tbody>[/TABLE]


Cells are left empty on weekends, and cells that show 0 mean the person is available to work. The Raw Data tab is where it says which project a resource is booked to, their rate and so on, so this is only to see whether someone is available.

Currently, the count for Bench time in column B is looking at how many times the total is less than 0 for the rest of this year, but we actually only need to know how many times it's less than 0 in the next 30 days of the cells that are not blank. In this example, and to save us from looking at something ridiculous, perhaps we could say we need to look at time in the next 3 days?

I keep thinking the answer should be simple, but I have spent too long looking at it now... Hoping someone here can help save my sanity!

Please let me know if more information is required, or if there's anything I've left unclear.