Excel COUNTIF question

CR4 Home | Software & Programming | Software & Programming

8 comments

Rate

rlindey Power-User Join Date: Sep 2006 Location: CT Posts: 267 Good Answers: 1	Excel COUNTIF question 11/16/2010 2:51 PM
	Greetings all. I have an excell spread sheet with several pages. One page has 2 columns with over 6000 rows. Another page has summation data gleamed from several other pages withinthe workbook. From the 2 column page, Column A refers to a location and Column B refers to a billing code. On the sumation page I want to import the number of like items from the first page dependant on location by code.(the codes repeat for each site). I can do this with the "COUNTIF" statement and limit the search by denoting the rows to count from. The problem is that each month I will get another page of data with a dfferent number of rows and will have to modify the search criteria to the the rows for each site. I do not want to have to go and modify the search criteria each month because basically I am lazy. Can any of you excel experts give me some help in automating the this? Thanks in advance Bob __________________ “Sometimes we don't even realize what we really care about, because we get so distracted by the symbols.” ? Tom Wolfe, The Electric Kool-Aid Acid Test
	Register to Reply

Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Join CR4, The Engineer's Place for News and Discussion!

Good Answers:

These comments received enough positive votes to make them "good answers".

#3 "Re: Excel COUNTIF question" by wasaadeh on 11/17/2010 2:33 AM (score 2)
#7 "Re: Excel COUNTIF question" by Schipper on 11/17/2010 9:37 AM (score 2)

"Almost" Good Answers:

Check out these comments that don't yet have enough votes to be "official" good answers and, if you agree with them, vote them!

#4 "Re: Excel COUNTIF question" by Anonymous Poster on 11/17/2010 3:07 AM (score 1)

Tornado Guru Join Date: May 2009 Location: Richland, WA, USA Posts: 21017 Good Answers: 795	#1 Re: Excel COUNTIF question 11/16/2010 8:43 PM
	Can you get what you want by specifying a ridiculously high range of rows, more than the spreadsheet is likely to grow? __________________ In vino veritas; in cervisia carmen; in aqua E. coli.
	Register to Reply

chrisg288 Guru Join Date: Sep 2006 Location: Clive, Alberta, Canada Posts: 5916 Good Answers: 204	#2 Re: Excel COUNTIF question 11/16/2010 11:04 PM
	Hi Bob, I've done data set extraction from long lists, as you seem to be describing, pulling together quotations.. but I'm a bit fuzzy on your criteria. I would appreciate if you could explain the application a bit more. I did mine all in vba, and didn't use countif... I find vba much more powerful. if you wish to send me your sheet, write to me at chrisg288 at hotmail. (I'm using excel 2008, so you may have to save as older) any more info will help. Chris
	Register to Reply

wasaadeh Associate Join Date: Sep 2008 Posts: 54 Good Answers: 1	#3 Re: Excel COUNTIF question 11/17/2010 2:33 AM
	Can't you specify the whole column range in the countif range field? i mean just put "A:A" in the search field of the countif. (not necessarely A:A, the letter corresponding to your column) if you can post somehow an example of your sheet, maybe we can write to you a VBA code that will do exactly what you desire. let me know if this helps
	Register to Reply Good Answer (Score 2)

Go to Next Good Answer

bp01 Power-User Join Date: Mar 2006 Location: In the middle of the USA Posts: 334 Good Answers: 14	#6 In reply to #3 Re: Excel COUNTIF question 11/17/2010 8:39 AM
	Chrisg and waasdeh are absolutley right about VBA being much more powerful, but for the situation described, specifying the column [=countif(A:A, ",criteria.")] is the best solution here: quick, easy, and accurate. __________________ "Reality is merely an illusion, albeit a very persistent one." -- Albert Einstein
	Register to Reply

Anonymous Poster	#4 Re: Excel COUNTIF question 11/17/2010 3:07 AM
	Hi, Try to use a Pivot Table, it will do most of the job. Best regards, Jorge.
	Register to Reply Score 1 for Good Answer

No more "Almost" Good Answers.

Javier Aseguinolaza Associate Join Date: Feb 2010 Location: Spain Posts: 33	#5 Re: Excel COUNTIF question 11/17/2010 3:08 AM
	You can calculate the number of rows with data content either in a cell or with a macro, then use a macro to do the searching. You can also link the macro to a button just in the same sheet you are doing the search and define a destination for this search in the same sheet, in a different one or in a new workbook. __________________ If you want different results, don't do always the same (A. Einstein)
	Register to Reply

Schipper Participant Join Date: Jul 2009 Posts: 1 Good Answers: 1	#7 Re: Excel COUNTIF question 11/17/2010 9:37 AM
	I agree with the posts that recommend selecting the entire column for the search - it doesn't get any easier than that. However, there is pretty cool way to always have your search domain go down through the last row with data. You can do this by searching a named range that is defined using the OFFSET function. See about the third topic in the link below. http://www.exceluser.com/explore/dynname1.htm If this one does not help you, do a search on "Excel OFFSET". Basically, you use COUNT to count all the rows that have data and then use that result to define the length of the column of data for your named range.
	Register to Reply Good Answer (Score 2)

No more Good Answers. Go to first "Almost" Good Answer

rlindey

Power-User

Join Date: Sep 2006

Location: CT

Posts: 267

Good Answers: 1

Re: Excel COUNTIF question

11/21/2010 7:45 AM

Sorry for not getting back sooner, On vacation you know.

I do not think I made my self very clear Here is a sample structurally that is similar to the actual.

Summary Page looks like this

Loaction A
Code	Frequency
12	3
14	1
16	0
17	0
123	2
Location B
12	1
14	2
16	1
17	2
123	1

Sheet two Data

Location	Code
a	123
a	12
b	14
b	123
b	16
a	12
b	14
a	12
b	12
b	17
b	17
a	14
a	123

How do I get the frequency to populate (using one formula) from the DataSource table. The actual source table has about 6000 rows and 125 codes. Next month number of data rows will be diiferent, It is currently unknown. I did get this to work by sorting the data on location then limiting the countif statement to the range of locations (=COUNTIF(DataSource1!B$1:DataSource!B$728,A3) for location "a" and (=COUNTIF(DataSource1!B$729:DataSource!B$6423,A3)for loacation "b").

What I would like to do is get a formula that i can past in for say 7000 rows on 12 monthly sheets that willcount code by location without having to sort each month and then modify the countif criteria.

__________________
“Sometimes we don't even realize what we really care about, because we get so distracted by the symbols.” ? Tom Wolfe, The Electric Kool-Aid Acid Test