# SUMPRODUCT to exclude text

#### keaton1ao

##### New Member
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).

##### MrExcel MVP
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
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))))``````

##### MrExcel MVP
Works beautifully. Thank you very much!

You are welcome. Thanks for providing feedback.

Replies
40
Views
766
Replies
20
Views
747
Replies
2
Views
227
Replies
2
Views
380
Replies
0
Views
189

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.

### Which adblocker are you using?

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

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