# Sumif Multiple Criteria

#### Rob #4

##### Board Regular
=SUM(IF(FREQUENCY(IF('Resident Worker Hour Report'!\$A\$19:\$A\$400=\$D13,IF('Resident Worker Hour Report'!\$B\$19:\$B\$400=\$B13,IF('Resident Worker Hour Report'!\$K\$4:\$DJ\$4=E\$9,IF('Resident Worker Hour Report'!\$K\$19:\$DJ\$400<>"",MATCH('Resident Worker Hour Report'!\$D\$19:\$D\$400&'Resident Worker Hour Report'!\$C\$19:\$C\$400,'Resident Worker Hour Report'!\$D\$19:\$D\$400&'Resident Worker Hour Report'!\$C\$19:\$C\$400,0))))),ROW('Resident Worker Hour Report'!\$A\$19:\$A\$400)-ROW('Resident Worker Hour Report'!\$A\$19)+1),1))

Someone helped me create this formula. It basicallly counts the number of people who Have the Same Work Class, Same Company and Worked Hours in a Particular Month. Now I am trying to total the hours for the people it counted. Any ideas?

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Rob #4

##### Board Regular
=SUM(IF('Resident Worker Hour Report'!\$A\$19:\$A\$400=\$D13,IF('Resident Worker Hour Report'!\$B\$19:\$B\$400=\$B13,IF('Resident Worker Hour Report'!\$K\$4:\$DY\$4=E\$9,SUM('Resident Worker Hour Report'!\$K\$19:\$DY\$400)))))

This is basically what I am trying to get to, but it is not working right.

If a persons criteria meets this:
IF A19:A400 = D13 and
IF B19:B400 = B13 and
IF K4:DY4 = E9

Then SUM the cells that fall into these areas

#### Rob #4

##### Board Regular
A possible another attempt:

=SUMPRODUCT(--('Resident Worker Hour Report'!\$A\$19:\$A\$400=\$D13,--('Resident Worker Hour Report'!\$B\$19:\$B\$400=\$B13,--('Resident Worker Hour Report'!\$K\$4:\$DY\$4=E\$9,('Resident Worker Hour Report'!\$K\$19:\$DY\$400))))

#### MrKowz

##### Well-known Member
Mismatched parenthases:

=SUMPRODUCT(--('Resident Worker Hour Report'!\$A\$19:\$A\$400=\$D13),--('Resident Worker Hour Report'!\$B\$19:\$B\$400=\$B13),--('Resident Worker Hour Report'!\$K\$4:\$DY\$4=E\$9),'Resident Worker Hour Report'!\$K\$19:\$DY\$400)

#### Rob #4

##### Board Regular

=SUMPRODUCT(--('Resident Worker Hour Report'!\$A\$19:\$A\$400=\$D13),--('Resident Worker Hour Report'!\$B\$19:\$B\$400=\$B13),--('Resident Worker Hour Report'!\$K\$4:\$DY\$4=E\$9),('Resident Worker Hour Report'!\$K\$19:\$DY\$400))

With this I still get a #VALUE error and I tried hitting control, shift, enter.

#### MrKowz

##### Well-known Member
Ahh - I see what's wrong -

=SUMPRODUCT(--('Resident Worker Hour Report'!\$A\$19:\$A\$400=\$D13),--('Resident Worker Hour Report'!\$B\$19:\$B\$400=\$B13),--('Resident Worker Hour Report'!\$K\$4:\$DY\$4=E\$9),('Resident Worker Hour Report'!\$K\$19:\$DY\$400))

Can't do that. You must have identically sized arrays for SUMPRODUCT to work.

#### Rob #4

##### Board Regular

I am trying to add columns K thru DY if the person meets the criteria. The headings at the top of columns K thru DY are in Months Jan '11, Feb '11 etc.

#### MrKowz

##### Well-known Member
Try:

=SUMPRODUCT(--(\$A\$19:\$A\$400=\$D\$13),--(\$B\$19:\$B\$400=\$B\$13),INDEX(\$K\$19:\$DY\$400,,MATCH(\$E\$13,\$K\$4:\$DY\$4,0)))

#### Rob #4

##### Board Regular
Here is my final version, but it returns the number 0, which is not correct.

=SUMPRODUCT(--('Resident Worker Hour Report'!\$A\$19:\$A\$400=\$D13),--('Resident Worker Hour Report'!\$B\$19:\$B\$400=\$B13),INDEX('Resident Worker Hour Report'!\$K\$19:\$DY\$400,,MATCH(E\$9,'Resident Worker Hour Report'!\$K\$4:\$DY\$4,0)))

Any ideas?

#### MrKowz

##### Well-known Member
It works fine on my end. I normally do not request this, but would you be able to e-mail me the file so I can take a look at the data and make sure it isn't a problem with formatting or something more underlying? If so, send me a PM and I will toss you my e-mail addy.

If not, please provide a better scope of your data that includes all of your input cells, as well as a sample of your data, including the row where you are testing E9?

Replies
0
Views
42
Replies
78
Views
1K
Replies
8
Views
87
Replies
4
Views
62
Replies
12
Views
123