CountIF() in Worksheet B while referencing cells in Worksheet A

[jfj3rd]

Hi everyone,

So my title might be the best way to answser my questionb ut I'll give
it a longer description here.

I don't want to clutter worksheetA with hidden fields containing
formulas that I would then call in WorksheetB.

So how do I tell CountIF to calculate specified cells in a different
worksheet?

Your help would be appreciative. I searched through the help file but
just simply don't know what I'd be looking for.

JJ


--
jfj3rd

[pdberger]

JF --

You can do this, either the regular way by using the mouse or, better, by
naming the range of data you want to pull from. Let's say you named the
range 'SourceData'. Here's the spreadsheet:

A B
1 XYZ =countif(SourceData,A1)
2 YZA =countif(SourceData,A2)

If you don't want to name the range then, as you create the countif formula,
just go to the other worksheet and select the range of the data source.
Remember to make the reference absolute before you copy it (which is why
naming it is best).

HTH

"jfj3rd" wrote:

>
> Hi everyone,
>
> So my title might be the best way to answser my questionb ut I'll give
> it a longer description here.
>
> I don't want to clutter worksheetA with hidden fields containing
> formulas that I would then call in WorksheetB.
>
> So how do I tell CountIF to calculate specified cells in a different
> worksheet?
>
> Your help would be appreciative. I searched through the help file but
> just simply don't know what I'd be looking for.
>
> JJ
>
>
> --
> jfj3rd
>

[jfj3rd]

PDBerger,

Thanks for the advice. It is stupid how such simple things can elude me. I'm so busy looking for difficult answer when an easy one is right in front of me!

Now that I know how to do that I have another obstacle to tackle.

My clients have two items that I track and I currently have 90 clients. These items are found in individual rows.

Column A is not a consideration in my delima, Column B identifies a client with a number (Not needed either IMO). Column C is useful.

Column C repeats this pattern.

Client Name
Client URL
#1 Item
#2 Item
Client Name
Client URL
#1 Item
#2 Item

So on and so forth with the exception that sometimes a client may have more than 2 items.

Column D & E are also not needed for this. They simply provide sign up date and expiration date.

The next three columns is the data I need to easily tally.

April March
Client Name Google Yahoo MSN Google Yahoo MSN
Client URL
#1 Item 4 12 29 8 17 13
#2 Item 8 1 20 5 2 13
Client Name
Client URL
#1 Item 13 9 1 11 19 2
#2 Item 48 3 108 78 10 0
Client Name
Client URL
#1 Item 3 9 13 3 9 13
#2 Item 1 1 2 1 1 2
#3 Item 18 3 16 18 3 16

That is the data that is collected. Every month we get a report that displays Growth / Loss %. This is only based on #1 Item though.

Making this further difficult, As you noticed, The collumns repeat themselfs with the exception of old data moving to the right. Eventually Columns F, G & H come to represent a new month and I, J & K represent the previous month and so on and so forth.

And as much as I hate to say it, making this even further difficult, our %'s are reported as follows:

Clients after 8 months
Clients after 2 years
Clients between 2 and 3 years
Clients between 1 and 2 years
Clients between 8 months and 1 year


Occasionally there is a break in the rows after clients to specify:
"3 YEARS (Clients above this line have had a Marketing Package 3 years or longer) 3 YEARS"

and so fourth.

I'd like to automate this proccess so I don't have to do all the tallying myself. The CountIF() is great but what esle should I be looking at to figure out this jumble of goodness? (Sarcamn on the goodness)

Any comments would be appreciative.

JJ

[pdberger]

JF --
I don't think your table translated to this format very well and, at any
rate, I'm having trouble understanding it. To me, it looks like your data is
three-dimensional. Maybe you could create a separate worksheet for each
month, creating 3-D formulas going back the proper number of months and then
copying them to successive pages.

Sorry I can't help more.



"jfj3rd" wrote:

>
> PDBerger,
>
> Thanks for the advice. It is stupid how such simple things can elude
> me. I'm so busy looking for difficult answer when an easy one is right
> in front of me!
>
> Now that I know how to do that I have another obstacle to tackle.
>
> My clients have two items that I track and I currently have 90 clients.
> These items are found in individual rows.
>
> Column A is not a consideration in my delima, Column B identifies a
> client with a number (Not needed either IMO). Column C is useful.
>
> Column C repeats this pattern.
>
> Client Name
> Client URL
> #1 Item
> #2 Item
> Client Name
> Client URL
> #1 Item
> #2 Item
>
> So on and so forth with the exception that sometimes a client may have
> more than 2 items.
>
> Column D & E are also not needed for this. They simply provide sign up
> date and expiration date.
>
> The next three columns is the data I need to easily tally.
>
> April March
> Client Name Google Yahoo MSN Google Yahoo MSN
> Client URL
> #1 Item 4 12 29 8 17
> 13
> #2 Item 8 1 20 5 2
> 13
> Client Name
> Client URL
> #1 Item 13 9 1 11 19
> 2
> #2 Item 48 3 108 78 10
> 0
> Client Name
> Client URL
> #1 Item 3 9 13 3 9
> 13
> #2 Item 1 1 2 1 1
> 2
> #3 Item 18 3 16 18 3
> 16
>
> That is the data that is collected. Every month we get a report that
> displays Growth / Loss %. This is only based on #1 Item though.
>
> Making this further difficult, As you noticed, The collumns repeat
> themselfs with the exception of old data moving to the right.
> Eventually Columns F, G & H come to represent a new month and I, J & K
> represent the previous month and so on and so forth.
>
> And as much as I hate to say it, making this even further difficult,
> our %'s are reported as follows:
>
> Clients after 8 months
> Clients after 2 years
> Clients between 2 and 3 years
> Clients between 1 and 2 years
> Clients between 8 months and 1 year
>
>
> Occasionally there is a break in the rows after clients to specify:
> "3 YEARS (Clients above this line have had a Marketing Package 3 years
> or longer) 3 YEARS"
>
> and so fourth.
>
> I'd like to automate this proccess so I don't have to do all the
> tallying myself. The CountIF() is great but what esle should I be
> looking at to figure out this jumble of goodness? (Sarcamn on the
> goodness)
>
> Any comments would be appreciative.
>
> JJ
>
>
> --
> jfj3rd
> ------------------------------------------------------------------------
> jfj3rd's Profile: http://www.excelforum.com/member.php...o&userid=33503
> View this thread: http://www.excelforum.com/showthread...hreadid=533007
>
>