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.

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.

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.

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.

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.

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.

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