I am trying to find out how many businesses a

Microsoft Excel 2002 - complete/upgrade...
March 5, 2011 at 05:48:32
Specs: Windows 7
I am trying to find out how many businesses are either churches, schools, or clinics, etc . using the countif function , please help.

CustomerName Cust Type Bill Waived Taxable
Amity Church NPROFIT Yes No 2010 1 55,160 0
Churchof Christ NPROFIT Yes No 2010 1 32,430 $0.000
NM State Highway GOV No No 2010 1 95,660 $143.49
Spot Free Power Wash COM No Yes 2010 1 281,200
CIT Apartment ComplexCOM No Yes 2010 1 797.99
Housing Agency GOV No No 2010 1 23,300 $0.000
Pinon Pizza Edge COM No Yes 2010 1 81,110
Peabody TC COM No Yes 2010 1 2,072,000 433.56
Business Affairs GOV No No 2010 1 3,978,000
O B Trailer Service COM No Yes 2010 1 360,800
Red Mesa Express COM No Yes 2010 1 392,600
Jimmy's Pizza COM No Yes 2010 1 895,300


See More: I am trying to find out how many businesses a

Report •

#1
March 5, 2011 at 06:31:26
Read this How-To to learn how to post your data so everything stays lined up and looks correct:

http://www.computing.net/howtos/sho...

If your planning on using the CUST TYPE column and use only those strings to find your totals then:

=COUNTIF(B1:B13,"NPROFIT") will get you the total number of NPROFIT's
=COUNTIF(B1:B13,"GOV") will get you the number number of GOV

MIKE

http://www.skeptic.com/


Report •

#2
March 5, 2011 at 06:47:53
That is great as well but i still am needing to know total of each, there is many many more in my customer field so a total number of pizza business, rv parks, etc. thank you again for assisting.

Report •

#3
March 5, 2011 at 07:29:22
Not sure what it is your asking for.

Without knowing where your data comes from,
I can see problems with Names not being consistent.

You may have a "church" without having the specific string "chuch" in it's name,
First Baptist or
Unitarian Universalist or
Kings Chapel

In your example for GOV you have:
NM State Highway which we can tell is a State Agency but what about:
Housing Agency the string Housing could denote a non-profit, and the string agency could be a talent agency.


MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 5, 2011 at 07:46:37
Wow thanks...I see i have to go back and make sure all my customer names have something consistant such as a pizza place needs to have the word Pizza in the customer name and if its a church I need to have the word church, etc. So after doing this would i use the same type of formula for each category? I do like your way but I know I am still needing the totals buy type of business. Thank you again!

Report •

#5
March 5, 2011 at 07:52:36
ok...so now I just have to use the * as the wild card so *church is this right?

Report •

#6
March 5, 2011 at 08:14:30
Actually something like:

=COUNTIF(A2:A13,"*pizza*")

That way you get both pizza shops.

MIKE

http://www.skeptic.com/


Report •

#7
March 5, 2011 at 08:24:58
You could also do something like:

=COUNTIF(A2:A13, "*" & H1 & "*" )

=IF(ISBLANK(H1),"",COUNTIF(A2:A13,"*"&H1&"*"))

Put the above formula in cell H2

Then enter the string your searching for in Cell H1
Then you need only enter your string once and
you get your total for that string.
That way you need not have separate formulas for all your possible strings.

MIKE

http://www.skeptic.com/


Report •

#8
March 5, 2011 at 08:54:19
Great ! Thank you for both options, this really helps I appreciate your help!

Report •

Ask Question