SUMPRODUCT to exclude text

keaton1ao

New Member
Joined
Jun 15, 2012
Messages
2
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Control+shift+enter, not just enter:
Rich (BB code):
=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

New Member
Joined
Jun 15, 2012
Messages
2
Works beautifully. Thank you very much!


Control+shift+enter, not just enter:
Rich (BB code):
=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))))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,430
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top