Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 Last edited by jfj3rd : April 14th 06 at 06:32 PM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make multiple cells in 1 worksheet equal multiple cells in another | Excel Worksheet Functions | |||
How can I use open/close Grouped cells in a Protected Worksheet? | Excel Discussion (Misc queries) | |||
linking cells in one worksheet to cells in another worksheet | Excel Worksheet Functions | |||
Reference cells from another worksheet problem | Excel Discussion (Misc queries) | |||
How can I merge unlocked cells in a worksheet that has been protec | Excel Discussion (Misc queries) |