SumIfs problem

**krombie19** · 07-25-2012, 03:41 PM

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?

**ChuckYeager** · 07-25-2012, 03:50 PM

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

**krombie19** · 07-25-2012, 03:52 PM

Sumifs is a new formula in Excel 2010

**FDibbins** · 07-25-2012, 04:15 PM

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?

**NBVC** · 07-25-2012, 04:22 PM

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)

**krombie19** · 07-25-2012, 04:31 PM

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.