|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 05:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com