software

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?--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=512818=SUM(IF((A1:A10=331)*(B1:B10lt;gt;quot;quot;),1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;JR573PUTTquot; gt; wrote
in message ...
gt;
gt; I have 2 sheets, one summary, and one detail.
gt;
gt; The detail is as follows:
gt;
gt;
gt; Dept units
gt; 331 12
gt; 331 24
gt; 331
gt; 331 12
gt; 332
gt; 332 36
gt; 332 24
gt; 333
gt;
gt; The summary is as follows:
gt;
gt;
gt; Dept # of styles
gt; 331 3
gt; 332 2
gt; 333 0
gt;
gt; I want the formula on the summary sheet to count the number of non
gt; blank entries for each dept.
gt;
gt; Which formula is it?
gt;
gt;
gt; --
gt; JR573PUTT
gt; ------------------------------------------------------------------------
gt; JR573PUTT's Profile:
www.excelforum.com/member.php...oamp;userid=31587
gt; View this thread: www.excelforum.com/showthread...hreadid=512818
gt;

No way this would work, does not reference the detail sheet???--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=512818
You just need to modify Bob's formula.=SUM(IF((YourDetailSheetName!A1:A10=331)*(YourDeta ilSheetName!B1:B10lt;gt;quot;quot;),1))

Again, it is an array formula so commit with Ctrl-Shift-Enter

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=512818
SteveG,

You are awesome, thank you, it worked, thanks much.--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=512818Like we were told that!

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;JR573PUTTquot; gt; wrote
in message ...
gt;
gt; No way this would work, does not reference the detail sheet???
gt;
gt;
gt; --
gt; JR573PUTT
gt; ------------------------------------------------------------------------
gt; JR573PUTT's Profile:
www.excelforum.com/member.php...oamp;userid=31587
gt; View this thread: www.excelforum.com/showthread...hreadid=512818
gt;

You were right, the array function worked perfectly, and you got the
right answer 1st, so thanks again and sorry I did not catch on quick
enough!--
JR573PUTT
------------------------------------------------------------------------
JR573PUTT's Profile: www.excelforum.com/member.php...oamp;userid=31587
View this thread: www.excelforum.com/showthread...hreadid=512818=SUM((A1:A10=331)*(B1:B10lt;gt;quot;quot;))

Biff

quot;Bob Phillipsquot; gt; wrote in message
...
gt; =SUM(IF((A1:A10=331)*(B1:B10lt;gt;quot;quot;),1))
gt;
gt; which is an array formula, it should be committed with Ctrl-Shift-Enter,
gt; not
gt; just Enter.
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;JR573PUTTquot; gt; wrote
gt; in message ...
gt;gt;
gt;gt; I have 2 sheets, one summary, and one detail.
gt;gt;
gt;gt; The detail is as follows:
gt;gt;
gt;gt;
gt;gt; Dept units
gt;gt; 331 12
gt;gt; 331 24
gt;gt; 331
gt;gt; 331 12
gt;gt; 332
gt;gt; 332 36
gt;gt; 332 24
gt;gt; 333
gt;gt;
gt;gt; The summary is as follows:
gt;gt;
gt;gt;
gt;gt; Dept # of styles
gt;gt; 331 3
gt;gt; 332 2
gt;gt; 333 0
gt;gt;
gt;gt; I want the formula on the summary sheet to count the number of non
gt;gt; blank entries for each dept.
gt;gt;
gt;gt; Which formula is it?
gt;gt;
gt;gt;
gt;gt; --
gt;gt; JR573PUTT
gt;gt; ------------------------------------------------------------------------
gt;gt; JR573PUTT's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31587
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=512818
gt;gt;
gt;
gt;