| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproducts, Counta Lookup Ref Formulas
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: http://www.excelforum.com/member.php...o&userid=31587 View this thread: http://www.excelforum.com/showthread...hreadid=512818 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproducts, Counta Lookup Ref Formulas
=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" wrote in message ... 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: http://www.excelforum.com/member.php...o&userid=31587 View this thread: http://www.excelforum.com/showthread...hreadid=512818 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproducts, Counta Lookup Ref Formulas
No way this would work, does not reference the detail sheet??? -- JR573PUTT ------------------------------------------------------------------------ JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587 View this thread: http://www.excelforum.com/showthread...hreadid=512818 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproducts, Counta Lookup Ref Formulas
You just need to modify Bob's formula. =SUM(IF((YourDetailSheetName!A1:A10=331)*(YourDeta ilSheetName!B1:B10<""),1)) Again, it is an array formula so commit with Ctrl-Shift-Enter Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=512818 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproducts, Counta Lookup Ref Formulas
SteveG, You are awesome, thank you, it worked, thanks much. -- JR573PUTT ------------------------------------------------------------------------ JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587 View this thread: http://www.excelforum.com/showthread...hreadid=512818 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproducts, Counta Lookup Ref Formulas
Like we were told that!
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "JR573PUTT" wrote in message ... No way this would work, does not reference the detail sheet??? -- JR573PUTT ------------------------------------------------------------------------ JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587 View this thread: http://www.excelforum.com/showthread...hreadid=512818 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproducts, Counta Lookup Ref Formulas
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: http://www.excelforum.com/member.php...o&userid=31587 View this thread: http://www.excelforum.com/showthread...hreadid=512818 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproducts, Counta Lookup Ref Formulas
=SUM((A1:A10=331)*(B1:B10<""))
Biff "Bob Phillips" wrote in message ... =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" wrote in message ... 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: http://www.excelforum.com/member.php...o&userid=31587 View this thread: http://www.excelforum.com/showthread...hreadid=512818 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup problem | Excel Worksheet Functions | |||
| Another way to lookup data | Excel Worksheet Functions | |||
| Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
| Lookup formulas | Excel Discussion (Misc queries) | |||
| How do I highlight all lookup formulas in a worksheet in one shot. | Excel Discussion (Misc queries) | |||
Hybrid Mode
