Unique count and totallying - 2 requests

[mahesh007]

Hello I have 2 different requests here.
1 - unique counting
need to do this using formulas and hopefully without needing to add any new columns.
need a formula to lookup the details sheet (for easyness i just added it as a new tab here) and count the unique names in a dept and display that in the summary sheet/tab
want the formula to be applied to the summary to go and lookup/count from the other sheet
have shown the expected results in the col "total unique heads"</SPAN></SPAN>

Dept</SPAN>	Total unique heads</SPAN>
123</SPAN>	3</SPAN>
456</SPAN>	3</SPAN>
789</SPAN>	2</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

Dept	Name
123	john
123	john
123	mark
456	john
456	joe
123	mary
456	sandy
789	carl
789	dough
899	tom

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

2 - summary totals
need to do this using formulas and hopefully without needing to add any new columns or pivot tables
there are 2 tabs here that correspond to 2 sheets, one the summary and one details</SPAN>
The details sheet is actually a separate workbook to which I may not really have any update rights and so have to look it up and summarize in various ways on to the summary tab/sheet</SPAN>

I need a formulas to lookup the details and summarize on various kinds of totals</SPAN>

• On the details if I can have the various totals in columns then great as its easy to show them on far right as information gets added to the sheet</SPAN>
• On the summary I can either pull from the details sheet totals or I can insert the formula directly here to look up and total from the details</SPAN>

I really don’t know how complicate this is to be frank or even if I am being clear.

</SPAN>

Dept</SPAN>	Work Type</SPAN>	Client</SPAN>
123</SPAN>	Project</SPAN>	either pull from Details sheet col F or insert formula here to calculate</SPAN>
123</SPAN>	Support</SPAN>	either pull from Details sheet col F or insert formula here to calculate</SPAN>
789</SPAN>	Maintenance</SPAN>	either pull from Details sheet col F or insert formula here to calculate</SPAN>
789</SPAN>	Support</SPAN>	either pull from Details sheet col F or insert formula here to calculate</SPAN>
456</SPAN>	Admin</SPAN>	either pull from Details sheet col F or insert formula here to calculate</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

Dept</SPAN>	Work Type</SPAN>	Hours</SPAN>
123</SPAN>	Project</SPAN>	22</SPAN>
123</SPAN>	Project</SPAN>	343</SPAN>		Total hrs by dept</SPAN>	?</SPAN>
123</SPAN>	Project</SPAN>	757</SPAN>		Total hrs by work type</SPAN>	?</SPAN>
123</SPAN>	Support</SPAN>	4</SPAN>		Total hrs by Dept and work type</SPAN>	?</SPAN>
123</SPAN>	Support</SPAN>	464</SPAN>			?</SPAN>
789</SPAN>	Maintenance</SPAN>	56</SPAN>			?</SPAN>
789</SPAN>	Maintenance</SPAN>	46</SPAN>
789</SPAN>	Support</SPAN>	12</SPAN>
789</SPAN>	Support</SPAN>	646</SPAN>
456</SPAN>	Admin</SPAN>	76</SPAN>
456</SPAN>	Admin</SPAN>	46</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=2><COL><COL></COLGROUP>

Thanks
mahesh

 

[Oeldere]

For other forummembers

This question is also asked on another forum.

See the link below.unique counting and total summaries - 2 requests

 

[iyyi]

Hello I have 2 different requests here.
1 - unique counting
need to do this using formulas and hopefully without needing to add any new columns.
need a formula to lookup the details sheet (for easyness i just added it as a new tab here) and count the unique names in a dept and display that in the summary sheet/tab
want the formula to be applied to the summary to go and lookup/count from the other sheet
have shown the expected results in the col "total unique heads"

Dept	Total unique heads
123	3
456	3
789	2

<tbody>
</tbody>

Dept	Name
123	john
123	john
123	mark
456	john
456	joe
123	mary
456	sandy
789	carl
789	dough
899	tom

<tbody>
</tbody>

mahesh

For the first question:
Add column C to details sheet with the formula in C2 =A2&B2 and drag down.
in cell B2 in the main sheet (Total unique heads) write:
=SUMPRODUCT(1/COUNTIF(details!C$2:C$11,details!C$2:C$11)*(A2=details!A$2:A$11))
and drag down to B4