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

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

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