SUMIFS with 3 criteria

[locopete999]

Hi Guys,

I need some help.

Normally this would be easy, but I've forgotten too much about sumifs (if that is the right one!)

I will describe my set up and what I'm trying to do

I have a table on sheet 1.

Dates down column A from A3 to A263 (Weekdays for a year)
Row 1 has employee initials, this is 2 cells per name (B2, C2 are both person A etc) This is from B2 to AM2
Row 2 is 2 columns per employee, repeating. The first is IN and has a 1 if the employee is in and 0 if they are off. The second is DONE. This contains the actual amount of a task done by the employee. The repeats from B2 to AM2 (In, Done, In, Done etc)

What I am then trying to do is create mini tables on subsequent worksheets to make productivity graphs per month.

So, on Sheet two I have names down column A, from A2 to A20.
I have IN down column B.
I have done down column C.

I have used the below forumla.

Please Login or Register  to view this content.

What I am trying to do, is on sheet 2, I want cell B2 to return the SUM of all of the IN values from the sheet 1 table, where the month is April, the column it is counting from is IN and the employee matches the initials on Sheet 2, cell A2. In the above instance, RT.

I can then use the same formula to add up the amout of the actual task done for that employee for that Month.

Can someone point me to where I'm going wrong please!

[JohnTopley]

For SUMIFS ranges have to be the same dimensions: look at SUMPRODUCT.

Better still , post a small sample file (not image).

[locopete999]

Thanks John,

I'm not familiar with SUMPRODUCT so i'm trying to find out how to upload a sample document

[Jonmo1]

Click 'Go Advanced', then scroll down and click 'Manage Attachments'

[locopete999]

Thanks Jonmo.

Hopefully this has worked!

[Jonmo1]

Try this in Sheet2 B2, filled down/right as needed

=SUMIFS(OFFSET(Sheet1!B$3:B$15,0,MATCH($A2,Sheet1!$B$1:$K$1,0)-1),Sheet1!$A$3:$A$15,">="&DATE(2016,4,1),Sheet1!$A$3:$A$15,"<="&DATE(2016,4,30))

[locopete999]

Thanks Jonmo!!

Its Looking good to me.

[Jonmo1]

You're welcome.

[JohnTopley]

Or

=SUMPRODUCT((Sheet1!$B$3:$K$15)*(Sheet1!$B$1:$K$1=Sheet2!$A2)*(Sheet1!$B$2:$K$2="IN"))

Requires names to be in each header cell i.e. cells unmerged.



You need to add IFERROR( ......, "") to Jonmo1.s formula to avoid #N/A errors

[Jonmo1]

I think you need to add criteria to that sumproduct regarding the date.
The sample file only showed April dates, but according to OP Column A has dates covering a whole year, and is interested only in April.

[JohnTopley]

Yes .. I missed the dates but SUMIF being better than SUMPRODUCT ......