CONDITIONAL COUNT

[techninut]

I have an issue I am trying to Iron out here.

Column 1 Column2

Mark Jentry GA
Chris Smith FL
mark harris OH
Mark Johnson GA
Jeff Smith TX
Mark LIonel GA

I am trying to count the number of people named Mark who live in GA. However, I am having trouble with pulling just "Mark" from colum 1. Does anyone know how I would accomplish this regardless of Mark being upper or lower case ?

 

[xlbo]

Have a look at the SUMPRODUCT function
there is a fAQ in the FAQs section

I would use

=SUMPRODUCT((UPPER(Left(A1:A100,4))="MARK")*(B1:B100="GA"))

where you have data in A1:B100

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[techninut]

Ok I apoligize, but I used Mark as just an example. In my situation Mark anywhere in the cell. example Johnson, Mark,
Mark in Marketing, etc. etc. therefore Mark will not be the first 4 letters in the cell. It could be like this:

al;fjaflkjLLLL Mark KKKKKKKK. So basically, I am trying to count the number of cells with Mark in it but they do not have any type of formatting. Unfortunately

 

[xlbo]

in which case

=SUMPRODUCT((NOT(ISERROR(FIND("MARK",UPPER(A1:A100)))))*(B1:B100="GA"))

should do the trick

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[techninut]

I put this in my formula and I received a circular ref. error

=SUMPRODUCT((NOT(ISERROR(FIND("MIN",UPPER(H1:H845)))))*(I1:I845="TYSON, ANDREY"))

any ideas ??

 

[xlbo]

well what cell is your formula in ??

If it is within H1:I845 then of course you will get a circular ref

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[techninut]

Ok. I put my code in J2 and did not get the error, however my value came out to zero, which it is not.


H I J
SiteName Name formula

A MEDICAL CENTE CI MIN 748042 PRICE, ROGER
MEDICAL OFFICE CI MAJ 661014 DETLEFS,KIRK
MEDICAL OFFICE CI MAJ 800118 VERIZON HAWAII
MEDICAL OFFICE CI MIN 747850 ROBINSON, SONYA
WINDWARD MEDICA CI MIN 645933 LOVE, AARON
KAI MEDICAL OFF CI MAJ 661004 BOYD,MORRIS
WINDWARD MEDICA CI MAJ 661004 BOYD,MORRIS
MEDICAL OFFICE CI MIN 653688 TYSON, ANDREY
MEDICAL CI MAJ 800118 IBM HAWAII
RVICE CENTER CI MIN 800118 IBM HAWAII
RVICE CENTER CI MIN 800118 IBM HAWAII
DICAL OFFICE CI MIN 661004 BOYD,MORRIS


these are the 2 columns of data I am working with. Basically I am trying to tie the name with the site based on if there is an MIN in the site Name.

The formula I entered is SUMPRODUCT((NOT(ISERROR(FIND("MIN",(H1:H845)))))*(I1:I845="TYSON, ANDREY"))


Just trying to put specifics in so it may be easier to work with.

 

[Zathras]

Another case for DATABASE techniques.

Set up a criteria range this way:
[tt]
SiteName Name
*MIN TYSON, ANDREY
[/tt]
and give it a range name of "criteria" then if your database has the range name of "database" then this formula correctly gives the count as 1:

=DCOUNTA(database,1,criteria)

 

[techninut]

If you could be more specific I would definitely appreciate it, as to I have no idea about excel. If this was in a database I would write an SQL query and have been done 3 hours ago . I am tempted to just build a pseudo access db and import my columns so I can just write and SQL statement.

 

[Guest_imported]

techninut,

I've modified an example file I previously created, with the modification now including the exact data you provided in your most recent example.

This file will show you how to set up the criteria. It's complete with notes that will help you greatly in understanding the use of Excel's "database formulas".

In addition, the file includes VBA code examples (attached to buttons) related to: filtering-in-place, extracting records, and deleting records - based on the same criteria created for the database formula(s).

I hope this helps.

Regards, Dale Watson

 

[xlbo]

Well it might help if you transposed the formula properly - you missed out the UPPER statement
SUMPRODUCT((NOT(ISERROR(FIND("MIN",(H1:H845)))))*(I1:I845="TYSON, ANDREY"))
should be:
SUMPRODUCT((NOT(ISERROR(FIND("MIN",UPPER(H1:H845)))))*(I1:I845="TYSON, ANDREY"))
and you may also want to make sure there are no trailing spaces in col B:
SUMPRODUCT((NOT(ISERROR(FIND("MIN",UPPER(H1:H845)))))*(TRIM(I1:I845)="TYSON, ANDREY"))

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question