Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Duplicates on two sheets
I have two sheets that are the same except for the sheet names, in one
column on both sheets B8:B107 can contain multiple duplicates. i have a column that uses this code to count how many duplicates of each record. =""& COUNTIF($B$8:B8,B8) What formula do i need to add that will count the duplicates that occur on sheet one and sheet two. Also on another note using this code is there some reason why i can't conditional format those cells and a zero value appears. i do have it to not show zero values as it hides other calculated cells on these sheets. Many Thanks in Advance Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Duplicates on two sheets
The below formula will count the number of entries colB for both the sheets.
=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"sheet1","sheet2 "}&"'!B:B"),B8)) If this post helps click Yes --------------- Jacob Skaria "Greg" wrote: I have two sheets that are the same except for the sheet names, in one column on both sheets B8:B107 can contain multiple duplicates. i have a column that uses this code to count how many duplicates of each record. =""& COUNTIF($B$8:B8,B8) What formula do i need to add that will count the duplicates that occur on sheet one and sheet two. Also on another note using this code is there some reason why i can't conditional format those cells and a zero value appears. i do have it to not show zero values as it hides other calculated cells on these sheets. Many Thanks in Advance Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Duplicates on two sheets
It does count duplicates on both sheets, like i described on first post.
I'm very sorry; I don't think i explained myself clear enough, i "assumed" the formula would explain. i was looking for a formula to count incremental like the formula i was using. if on sheet 1 there were two duplicates, in this column there would be a 1 and 2. if on sheet 2 i get that same duplicate, i would get 3 then 4, etc... when sheet one "day one" is complete no more information will go on that page. So Sheet 2 "day two" would pick up where sheet 1 left off. I'm very sorry for not explaining myself clear enough. and hope this post doesn't sound rude. it surly wasn't meant to be if it does. Thanks Again Greg "Jacob Skaria" wrote: The below formula will count the number of entries colB for both the sheets. =SUMPRODUCT(COUNTIF(INDIRECT("'"&{"sheet1","sheet2 "}&"'!B:B"),B8)) If this post helps click Yes --------------- Jacob Skaria "Greg" wrote: I have two sheets that are the same except for the sheet names, in one column on both sheets B8:B107 can contain multiple duplicates. i have a column that uses this code to count how many duplicates of each record. =""& COUNTIF($B$8:B8,B8) What formula do i need to add that will count the duplicates that occur on sheet one and sheet two. Also on another note using this code is there some reason why i can't conditional format those cells and a zero value appears. i do have it to not show zero values as it hides other calculated cells on these sheets. Many Thanks in Advance Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Duplicates Once | Excel Worksheet Functions | |||
Counting duplicates | Excel Discussion (Misc queries) | |||
counting function but not double counting duplicates | Excel Worksheet Functions | |||
Counting duplicates | Excel Discussion (Misc queries) | |||
counting duplicates Among Many Sheets, Possible?? | New Users to Excel |