Count distinct records that meet multiple conditions

**rubthebuddha** · 10-17-2013, 06:05 PM

I've come to a brick wall on a specific formula and I'm hoping someone has either a way to knock it down or at least work around it.

As part of a larger formula, I have a table from which I'm trying to count the number of people who, during each month, worked more than zero hours.

For example:

employee	month	hours_worked
david	1	0
david	2	5
david	3	9
mike	1	0
mike	2	0
mike	3	6

	month	worked
	1	0
	2	1
	3	2

The summary table in which I'd like the final counts to show has the month numbers as headers.

I've run out of math power for the day. If anyone has any insight, please share. And if I didn't title this in a way that makes sense, let me know and I'll adjust.

**Mazzaropi** · 10-17-2013, 06:40 PM

rubthebuddha, Good evening.

Using your layout:

DATA
Column A = employee
Column B = month
Column C = hours_worked

SUMMARY:
Column B = month
Column C = worked

DO:
C11 --> =SUMPRODUCT(($C$2:$C$7>0)*($B$2:$B$7=B11))
C12 --> =SUMPRODUCT(($C$2:$C$7>0)*($B$2:$B$7=B12))
C13 --> =SUMPRODUCT(($C$2:$C$7>0)*($B$2:$B$7=B13))

Is it what you desired?
I hope it helps.

**newdoverman** · 10-17-2013, 07:46 PM

Here are a couple of ways to calculate what you want:

**rubthebuddha** · 10-18-2013, 09:54 AM

I'm writing letters to Santa on behalf of both of you. However, I failed to mention that each person will have multiple records each month, hence the difficulty with me on the count distinct part.