Counting Duplicates on two sheets

[Greg]

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

 

[Jacob Skaria]

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

 

[Greg]

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