counting selected rows among those returned as "TRUE" by an array sum formula

  • Thread starter Thread starter upstate_steve
  • Start date Start date
U

upstate_steve

=SUM(IF((ptft="PT")*(class="S")*(date>=$A23)*(date<$A23+7),ConvHrs))

I use the preceding formula to get total hours worked by part tim
sales staff for a certain week. The database has a row for each da
(even days not worked) for each employee.

How do I get the number of part time sales employees who worked mor
than 0 hours that week?

That is to say, of the rows returning "TRUE" above, how many uniqu
values in the field EmpNo where ConvHrs>0
 
Hi

Wouldn't

=SUMPRODUCT((ptft="PT")*(class="S")*(date>=$A23)*(date<$A23+7)*(ConvHrs
0))
Click to expand...
give me the number of shifts worked, with most employees bein
represented by more than one shift?

Among that set of rows counted by your formula, how do I get the numbe
of unique values represented in the additional field EmpNo?

Thank
 
Hi
this is a little bit complicated without knowing a little bit more
about the design of your spreadsheet. Could you post some example rows
of data 8plain text - no attachment please) and explain your expected
result based on this example
 
Small Sample of Database:

Columns: EmpNo, ptft, class, date, ConvHrs

0001 PT S 02/29 6.0
0002 FT C 02/29 7.5
0003 FT S 02/29 7.5
0004 PT S 02/29 0.0
0001 PT S 03/01 5.0
0002 FT C 03/01 7.5
0003 FT S 03/01 7.5
0004 PT S 03/01 0.0
0001 PT S 03/02 0.0
0002 FT C 03/02 7.5
0003 FT S 03/02 7.5
0004 PT S 03/02 0.0
etc..................
etc..................
----------------------------------------------------------

Spreadsheet: "Avg Hrs Worked, PT Sales Reps, By Week"


.............A..........B.............C.............D
----------------------------------------------------------
22|.....week....totalhrs....# reps.....avg
23|.....2/29.....*...............?..........=B23/C23
24|.....3/06.....*...............?..........=B24/C24
25|.....3/13.....*...............?..........=B25/C25

*
=SUM(IF((ptft="PT")*(class="S")*(date>=$A23)*(date<$A23+7),ConvHrs))

In this formula, "ptft", "class", etc. are the appropriately named cel
ranges.

I want to determine the number of PT, "S" (Sales) employees with >
hours for the indicated week.

Assuming the 12 rows above represent the entire week, that number woul
be 1. (EmpNo 0001.)

=SUMPRODUCT((ptft="PT")*(class="S")*(date>=$A23)*(date<$A23+7)*(ConvHrs
would count both of 0001's qualifying rows and return "2"
 
Back
Top