IF/Subproduct Formula

[Rob #4]

Can someone help me fix this Formula:

=IF('Resident Worker Hour Report'!K19:DJ400>1),(SUMPRODUCT(--('Resident Worker Hour Report'!$B$19:$B$400="Company"),--('Resident Worker Hour Report'!$A$19:$A$400="Class"))

My Rows I have Workers Names, Company Name and Work Class. My Columns K thru DJ are calender weeks where I track the hours someone works each week. I want to count the number of people who work more then 1 hour for each Month. So Columns K thru N are January, O thru R are February, and so on.

Any ideas?

 

[barry houdini]

Do you have some sort of header row which gives start date of each week or similar. How do you calssify weeks which start in one month and end in another?

 

[Rob #4]

In Row 5, Starting with Column K I have the End of each Weeks Date.

 

[Rob #4]

I am getting closer to solving this. I have come up with this formula, but I have a feeling it can be simplified:

=SUMPRODUCT(--('Resident Worker Hour Report'!$B$19:$B$400="Independence Excavating"),--('Resident Worker Hour Report'!$A$19:$A$400="L"),--('Resident Worker Hour Report'!$K$19:$K$400>1))+SUMPRODUCT(--('Resident Worker Hour Report'!$B$19:$B$400="Independence Excavating"),--('Resident Worker Hour Report'!$A$19:$A$400="L"),--('Resident Worker Hour Report'!$L$19:$L$400>1))+SUMPRODUCT(--('Resident Worker Hour Report'!$B$19:$B$400="Independence Excavating"),--('Resident Worker Hour Report'!$A$19:$A$400="L"),--('Resident Worker Hour Report'!$M$19:$M$400>1))+SUMPRODUCT(--('Resident Worker Hour Report'!$B$19:$B$400="Independence Excavating"),--('Resident Worker Hour Report'!$A$19:$A$400="L"),--('Resident Worker Hour Report'!$N$19:$N$400>1))

Basically I have had to count each week seperatly and then add them together. The problem with this is, I get people counted multiple times. If someone works all 4 weeks, then they get counted 4 times. How can I modify this formula, to not count someone more then once? There as to be an easier way, since using this formula, I will have to modify the columns for each month. Any help would be appreciated. Thank you

 

[snoopyhr]

is it possible for you to show the setup of your data? hard to follow from the description

 

[MrRajKumar]

Try this Array Formula,

=SUM(IF('Resident Worker Hour Report'!$B$19:$B$400="Independence Excavating",IF('Resident Worker Hour Report'!$A$19:$A$400="L",IF('Resident Worker Hour Report'!$K$19:$N$400>1,1))))

Confirmed with Control+Shift+Enter, not just Enter.