Sumproduct - Do Not Count Duplicates

[Rob #4]

I have this formula which I am trying to count the number of workers who work in a month. I have a spreadsheet set up to record workers hours each week. This formula, I am sure could be made easier, but I can't figure out how, so this is where I am at. It counts how many people worked in Week 1, then counts how many people worked Week 2, plus Week 3, plus Week 4. What is doesn't reconize is duplicates. If someone worked all 4 weeks they count as 4, and I only want them to count once.

=SUMPRODUCT(--('Resident Worker Hour Report'!$B$19:$B$400="Independence Excavating"),--('Resident Worker Hour Report'!$A$19:$A$400="O"),--('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="O"),--('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="O"),--('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="O"),--('Resident Worker Hour Report'!$N$19:$N$400>1))

Any Ideas?

 

[Matty]

It looks from the formula you've posted that you're wanting to calculate the cumulative total rather than the total for each month. Given the set up illustrated in my previous post, this could be done as follows:

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 93px;"><col style="width: 202px;"><col style="width: 87px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>A</td><td>B</td><td>C</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">35</td><td style="font-family: Verdana;">Worker Class</td><td style="font-family: Verdana;">Company</td><td style="font-family: Verdana;">Total</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">36</td><td style="font-family: Verdana;">CA</td><td style="font-family: Verdana;">Goettle</td><td style="font-family: Verdana; text-align: right;">697.5</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">37</td><td style="font-family: Verdana;">CA</td><td style="font-family: Verdana;">Independence Excavating</td><td style="font-family: Verdana; text-align: right;">75.0</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">38</td><td style="font-family: Verdana;">L</td><td style="font-family: Verdana;">Ballast Fence</td><td style="font-family: Verdana; text-align: right;">13.0</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">39</td><td style="font-family: Verdana;">L</td><td style="font-family: Verdana;">Goettle</td><td style="font-family: Verdana; text-align: right;">229.0</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">40</td><td style="font-family: Verdana;">L</td><td style="font-family: Verdana;">Independence Excavating</td><td style="font-family: Verdana; text-align: right;">242.0</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">41</td><td style="font-family: Verdana;">O</td><td style="font-family: Verdana;">Goettle</td><td style="font-family: Verdana; text-align: right;">388.0</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">42</td><td style="font-family: Verdana;">O</td><td style="font-family: Verdana;">Independence Excavating</td><td style="font-family: Verdana; text-align: right;">309.0</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">43</td><td style="font-family: Verdana;">S</td><td style="font-family: Verdana;">Goettle</td><td style="font-family: Verdana; text-align: right;">360.0</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">44</td><td style="font-family: Verdana;">S</td><td style="font-family: Verdana;">Independence Excavating</td><td style="font-family: Verdana; text-align: right;">397.5</td></tr></tbody></table>
C36, array-entered and copied down:

=SUM(IF($A$19:$A$31=$A36,IF($B$19:$B$31=$B36,$K$19:$T$31)))

If you want to factor in months, the above can quickly be adapted.

Matty