Countif With Critiera

[JR573PUTT]

I have 2 sheets, one summary, and one detail.

The detail is as follows:


Dept units
331 12
331 24
331
331 12
332
332 36
332 24
333

The summary is as follows:


Dept # of styles
331 3
332 2
333 0

I want the formula on the summary sheet to count the number of non blank entries for each dept.

Which formula is it?

[gjcase]

Try Dcount, see replies to your earlier post.

[JR573PUTT]

How will Dcount reference the department?

[gjcase]

Assume Sheet1 contains your original data in cells A1:B9, including the headers "Dept" and "Units" (

Sheet2 contains your summary & Criteria

Criteria for dept 331 is specified as follows in D1:D2. Note "Dept" must exactly match the header in the table, including any spaces; it helps to copy/paste this to the criteria range.

Dept
331

The summary for Dept 331 contains the formula

=DCOUNT(Sheet2!$A$1:$B$9,2,D3:D4)

Same for the other depts

---Glenn

[gjcase]

Sorry, typo, the formula s/b as follows, to reference the range on sheet 1

=DCOUNT(Sheet1!$A$1:$B$9,2,D3:D4)