SUMPRODUCT to exclude text

[keaton1ao]

Hi all. I will do my best to explain what i have going on and what I would like. I'm 10+ hours into this spreadsheet already and I don't want to give up but I am stumped. I am making a spreadsheet for the schedule that I make at work and I want for it to tell me how many people are working during specific times. So for example: How many people do I have working at 5 am, 6 am, 7 am....I have two sheets on my excel workbook which I will upload. The second sheet is where it does all the "clock in, clock out" calculations and adds them up to the total number of hours worked each week, who is UV (unavailable to work that day) or has a SR (special request to not work that day). Sheet 1 just references all the info in sheet 2 to make it look nice and pretty and easier for me to read. Here is my problem. When I go to calculate how many people are working at a specific time the text values that I have written on sheet 2 bring back a #VALUE error in the table at the bottom of sheet 1.

I am using this formula to calculate how many people are working these specific times:
=SUMPRODUCT((Sheet2!C4:Sheet2!C47<=B51)*(MOD(Sheet2!D4:Sheet2!D47-Sheet2!C4:Sheet2!C47,1)>=MOD(Sheet2!D4:Sheet2!D47-B51,1)))
(if you open up my workbook you will see the error message in the field I want.

I hope that explains enough of what I need, I just need that formula to obviously ignore the text and blank fields of sheet 2 and return the value of how many people are working at those specific times. I'm pretty sure my formatting is all correct I just don't know a whole lot about array, true, false formulas to be able to figure this out. Thanks! (Attached link is my spreadsheet so you can see what I am talking about)

http://cl.ly/HPH7

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(
    IF(ISNUMBER(Sheet2!C4:C47*Sheet2!D4:D47),
    IF(Sheet2!C4:C47<=B51,
    IF(MOD(Sheet2!D4:D47-Sheet2!C4:C47,1)>=MOD(Sheet2!D4:D47-B51,1),
     1))))

Hi all. I will do my best to explain what i have going on and what I would like. I'm 10+ hours into this spreadsheet already and I don't want to give up but I am stumped. I am making a spreadsheet for the schedule that I make at work and I want for it to tell me how many people are working during specific times. So for example: How many people do I have working at 5 am, 6 am, 7 am....I have two sheets on my excel workbook which I will upload. The second sheet is where it does all the "clock in, clock out" calculations and adds them up to the total number of hours worked each week, who is UV (unavailable to work that day) or has a SR (special request to not work that day). Sheet 1 just references all the info in sheet 2 to make it look nice and pretty and easier for me to read. Here is my problem. When I go to calculate how many people are working at a specific time the text values that I have written on sheet 2 bring back a #VALUE error in the table at the bottom of sheet 1.

I am using this formula to calculate how many people are working these specific times:
=SUMPRODUCT((Sheet2!C4:Sheet2!C47<=B51)*(MOD(Sheet2!D4:Sheet2!D47-Sheet2!C4:Sheet2!C47,1)>=MOD(Sheet2!D4:Sheet2!D47-B51,1)))
(if you open up my workbook you will see the error message in the field I want.

I hope that explains enough of what I need, I just need that formula to obviously ignore the text and blank fields of sheet 2 and return the value of how many people are working at those specific times. I'm pretty sure my formatting is all correct I just don't know a whole lot about array, true, false formulas to be able to figure this out. Thanks! (Attached link is my spreadsheet so you can see what I am talking about)

http://cl.ly/HPH7

 

[keaton1ao]

Works beautifully. Thank you very much!

Control+shift+enter, not just enter:

=SUM(
    IF(ISNUMBER(Sheet2!C4:C47*Sheet2!D4:D47),
    IF(Sheet2!C4:C47<=B51,
    IF(MOD(Sheet2!D4:D47-Sheet2!C4:C47,1)>=MOD(Sheet2!D4:D47-B51,1),
     1))))

 

[Aladin Akyurek]

Works beautifully. Thank you very much!

You are welcome. Thanks for providing feedback.