# SumIfs problem

1. ## SumIfs problem

ok, here is the situation in Sheet 1 Column B I have the date that a person arrived. In Sheet 1 Column C I have the time the person arrived. In Sheet 3 Column A I have the the date, in Sheet 2 Column C I have the time the shift started and in Sheet 3 Column C I have the time the shift ended. In Sheet 3 Column D I have a number which indicates how many people were on that shift, some shift times overlap.

What I need to do is count how many people were working when the customer arrived and place that number in a column on sheet 1.

This is what I came up with but does not work properly.

=SUMIFS(Sheet3!\$D\$1:\$D\$605,Sheet3!\$A\$1:\$A\$605,"="&Sheet1!B2,Sheet3!\$B\$1:\$B\$605,"<="&Sheet1!C2,Sheet3!\$C\$2:\$C\$606,">="&Sheet1!C2)

Can someone give me some guidance?

2. ## Re: SumIfs problem

Hi Krombie,

Need to look at the example.

However you have put the formula as SUMIFS and not SUMIF, unless its a new formula that has been introduced in 2010

Most probably by now excel would point to you "~ too many arguments~" as SumIf only takes 3 arguments in a normal scenario.

Regards

Chuck

3. ## Re: SumIfs problem

Sumifs is a new formula in Excel 2010

4. ## Re: SumIfs problem

sumifs() was introduced in 2007, and im a little rusty, but i have a feeling it wont work across sheets.

as a test, copy/reference a portion of your data from your other sheet/s to sheet3 and then try it again based onthose values?

5. ## Re: SumIfs problem

your column C range is offset by one row from column B range...is that correct?

What is the problem? Is the formula not being allowed? Or are you getting incorrect results?

I entered the formula and it indicated a problem and suggested the below fix:

=SUMIFS(Sheet3!\$D\$1:\$D\$605,Sheet3!\$A\$1:\$A\$605,"="&Sheet1!B2,Sheet3!\$B\$1:\$B\$605,"<="&Sheet1!C2,Sheet3 !\$C\$2:\$C\$606,">="&Sheet1!C2)

6. ## Re: SumIfs problem

I was getting incorrect figures. I got it figured out though, my issue was that for the shift ending times I was not using 3:59:59 PM instead of 4:00:00 PM. But my formula was correct.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1