Sumproducts, Counta Lookup Ref Formulas

[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 no
blank entries for each dept.

Which formula is it

 

[Bob Phillips]

=SUM(IF((A1:A10=331)*(B1:B10<>""),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)

 

[JR573PUTT]

No way this would work, does not reference the detail sheet???

 

[SteveG]

You just need to modify Bob's formula.


=SUM(IF((YourDetailSheetName!A1:A10=331)*(YourDetailSheetName!B1:B10<>""),1))

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

Steve

 

[JR573PUTT]

SteveG,

You are awesome, thank you, it worked, thanks much.

 

[Bob Phillips]

Like we were told that!

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[JR573PUTT]

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!

 

[Biff]

=SUM((A1:A10=331)*(B1:B10<>""))

Biff

=SUM(IF((A1:A10=331)*(B1:B10<>""),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)