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>

[TABLE="width: 191"]
<TBODY>[TR]
[TD]Dept</SPAN>[/TD]
[TD]Total unique heads</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]123</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]456</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]789</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]


[TABLE="width: 96"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]Dept[/TD]
[TD="width: 64, bgcolor: transparent"]Name[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"] john[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"] john[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"] mark[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]456 [/TD]
[TD="bgcolor: transparent"] john[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]456[/TD]
[TD="bgcolor: transparent"] joe[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"] mary[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]456[/TD]
[TD="bgcolor: transparent"] sandy[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]789[/TD]
[TD="bgcolor: transparent"] carl[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]789[/TD]
[TD="bgcolor: transparent"] dough[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]899[/TD]
[TD="bgcolor: transparent"] tom[/TD]
[/TR]
</TBODY>[/TABLE]


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>
[TABLE="width: 599"]
<TBODY>[TR]
[TD]Dept</SPAN>[/TD]
[TD]Work Type</SPAN>[/TD]
[TD]Client</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]123</SPAN>[/TD]
[TD] Project</SPAN>[/TD]
[TD]either pull from Details sheet col F or insert formula here to calculate</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]123</SPAN>[/TD]
[TD] Support</SPAN>[/TD]
[TD]either pull from Details sheet col F or insert formula here to calculate</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]789</SPAN>[/TD]
[TD] Maintenance</SPAN>[/TD]
[TD]either pull from Details sheet col F or insert formula here to calculate</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]789</SPAN>[/TD]
[TD] Support</SPAN>[/TD]
[TD]either pull from Details sheet col F or insert formula here to calculate</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]456</SPAN>[/TD]
[TD] Admin</SPAN>[/TD]
[TD]either pull from Details sheet col F or insert formula here to calculate</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]


[TABLE="width: 591"]
<TBODY>[TR]
[TD]Dept</SPAN>[/TD]
[TD]Work Type</SPAN>[/TD]
[TD]Hours</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123</SPAN>[/TD]
[TD]Project</SPAN>[/TD]
[TD="align: right"]22</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123</SPAN>[/TD]
[TD]Project</SPAN>[/TD]
[TD="align: right"]343</SPAN>[/TD]
[TD][/TD]
[TD]Total hrs by dept</SPAN>[/TD]
[TD]?</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]123</SPAN>[/TD]
[TD]Project</SPAN>[/TD]
[TD="align: right"]757</SPAN>[/TD]
[TD][/TD]
[TD]Total hrs by work type</SPAN>[/TD]
[TD]?</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]123</SPAN>[/TD]
[TD]Support</SPAN>[/TD]
[TD="align: right"]4</SPAN>[/TD]
[TD][/TD]
[TD]Total hrs by Dept and work type</SPAN>[/TD]
[TD]?</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]123</SPAN>[/TD]
[TD]Support</SPAN>[/TD]
[TD="align: right"]464</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]?</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]789</SPAN>[/TD]
[TD]Maintenance</SPAN>[/TD]
[TD="align: right"]56</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]?</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]789</SPAN>[/TD]
[TD]Maintenance</SPAN>[/TD]
[TD="align: right"]46</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]789</SPAN>[/TD]
[TD]Support</SPAN>[/TD]
[TD="align: right"]12</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]789</SPAN>[/TD]
[TD]Support</SPAN>[/TD]
[TD="align: right"]646</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]456</SPAN>[/TD]
[TD]Admin</SPAN>[/TD]
[TD="align: right"]76</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]456</SPAN>[/TD]
[TD]Admin</SPAN>[/TD]
[TD="align: right"]46</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=2><COL><COL></COLGROUP>[/TABLE]

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"

[TABLE="width: 191"]
<tbody>[TR]
[TD]Dept[/TD]
[TD]Total unique heads[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]456[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]789[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 96"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Dept[/TD]
[TD="width: 64, bgcolor: transparent"]Name[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"] john[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"] john[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"] mark[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]456 [/TD]
[TD="bgcolor: transparent"] john[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]456[/TD]
[TD="bgcolor: transparent"] joe[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]123[/TD]
[TD="bgcolor: transparent"] mary[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]456[/TD]
[TD="bgcolor: transparent"] sandy[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]789[/TD]
[TD="bgcolor: transparent"] carl[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]789[/TD]
[TD="bgcolor: transparent"] dough[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]899[/TD]
[TD="bgcolor: transparent"] tom[/TD]
[/TR]
</tbody>[/TABLE]
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