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

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

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

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

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

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

CountIF() in Worksheet B while referencing cells in Worksheet
 

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