Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell references in a countif function?
I have an extremely large file that I need to compare against another
extremely large file. I am using a countif function based on the criteria "account #". My issue is that my file contains accounts from my 12 different markets. I am using a match function now to provide me with the start cell for each of my markets and adjusting my countif formula range accordingly. Is there anyway to nest the match formula so that it automatically populates my countif ranges. For example: Market 1 starts on b1 Market 2 starts on b1000 Market 3 starts on b4000 =countif(match1:match2-1,"account") =countif(match2:match3-1,"account") and so on... Thanks in advance for the help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell references in a countif function?
Please tell use WHAT you need rather than HOW you are doing it because I
think there could be another way. Give as a sample (fake , if necessary) data set best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "WildlyHarry" wrote in message ... I have an extremely large file that I need to compare against another extremely large file. I am using a countif function based on the criteria "account #". My issue is that my file contains accounts from my 12 different markets. I am using a match function now to provide me with the start cell for each of my markets and adjusting my countif formula range accordingly. Is there anyway to nest the match formula so that it automatically populates my countif ranges. For example: Market 1 starts on b1 Market 2 starts on b1000 Market 3 starts on b4000 =countif(match1:match2-1,"account") =countif(match2:match3-1,"account") and so on... Thanks in advance for the help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell references in a countif function?
The data is pulled from my mainframe system and I have to bring it in as tab
delimited. It is an absolute mess. All I want to do is compare the number of accounts from one report to the next and I need to do it by my different markets. For example under the heading of market 1 there may be 120 accounts spread over 12000 rows. Each row does not contain a unique identifier only one row at the top of each record has the text "account". The information listed below that is useless until the next "account". Those "accounts" are what I need to count to compare to my other report which is set up the same way. I only know that these accounts show up in column b, I have no way of know what row that appear in. I have to do this everyday and everyday the report is completely different. Hence my need for a formula that will automatically define the count ranges based on the data on the spreadsheet. Hope this makes things clearer. "Bernard Liengme" wrote: Please tell use WHAT you need rather than HOW you are doing it because I think there could be another way. Give as a sample (fake , if necessary) data set best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "WildlyHarry" wrote in message ... I have an extremely large file that I need to compare against another extremely large file. I am using a countif function based on the criteria "account #". My issue is that my file contains accounts from my 12 different markets. I am using a match function now to provide me with the start cell for each of my markets and adjusting my countif formula range accordingly. Is there anyway to nest the match formula so that it automatically populates my countif ranges. For example: Market 1 starts on b1 Market 2 starts on b1000 Market 3 starts on b4000 =countif(match1:match2-1,"account") =countif(match2:match3-1,"account") and so on... Thanks in advance for the help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell references in a countif function?
Please elaborate; if the account is a unique identifier, why limit the
ranges in your countif statement? E.g., why not count the total number of times that account shows up in the entire column (B:B)? Thanks, Keith "WildlyHarry" wrote in message ... I have an extremely large file that I need to compare against another extremely large file. I am using a countif function based on the criteria "account #". My issue is that my file contains accounts from my 12 different markets. I am using a match function now to provide me with the start cell for each of my markets and adjusting my countif formula range accordingly. Is there anyway to nest the match formula so that it automatically populates my countif ranges. For example: Market 1 starts on b1 Market 2 starts on b1000 Market 3 starts on b4000 =countif(match1:match2-1,"account") =countif(match2:match3-1,"account") and so on... Thanks in advance for the help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell references in a countif function?
=SUMPRODUCT(--(A1:A1000="Market 1"),--(B1:B1000=account))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "WildlyHarry" wrote in message ... I have an extremely large file that I need to compare against another extremely large file. I am using a countif function based on the criteria "account #". My issue is that my file contains accounts from my 12 different markets. I am using a match function now to provide me with the start cell for each of my markets and adjusting my countif formula range accordingly. Is there anyway to nest the match formula so that it automatically populates my countif ranges. For example: Market 1 starts on b1 Market 2 starts on b1000 Market 3 starts on b4000 =countif(match1:match2-1,"account") =countif(match2:match3-1,"account") and so on... Thanks in advance for the help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell references in a countif function?
Bob: I do not think the data is arranged in a way that permits this.
But OP has not given a clean picture of the layout best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Bob Phillips" wrote in message ... =SUMPRODUCT(--(A1:A1000="Market 1"),--(B1:B1000=account)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "WildlyHarry" wrote in message ... I have an extremely large file that I need to compare against another extremely large file. I am using a countif function based on the criteria "account #". My issue is that my file contains accounts from my 12 different markets. I am using a match function now to provide me with the start cell for each of my markets and adjusting my countif formula range accordingly. Is there anyway to nest the match formula so that it automatically populates my countif ranges. For example: Market 1 starts on b1 Market 2 starts on b1000 Market 3 starts on b4000 =countif(match1:match2-1,"account") =countif(match2:match3-1,"account") and so on... Thanks in advance for the help. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell references in a countif function?
Hi Bernard,
I originally thought that it was more complex and it would need some sort of parser to get the start and end rows, but the more I read it, the more it looked simple. Only time (or the OP) will tell <bg. Bob "Bernard Liengme" wrote in message ... Bob: I do not think the data is arranged in a way that permits this. But OP has not given a clean picture of the layout best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumIf function with Multiple cell references | Excel Worksheet Functions | |||
IF function using dates as cell references | Excel Worksheet Functions | |||
Cannot get VALUE function to work with cell references | Excel Worksheet Functions | |||
Sumif function with remote cell references | Excel Worksheet Functions | |||
Using the MAX function with "constant increment" cell references | Excel Worksheet Functions |