COUNTIF

I need to set up a formula that will count a value if it meets a particular
criteria within a range.

Count if the number is less than 2
Count if the number is between the range of 3 & 5
Count if the number is between the range of 6 & 8
and so on....

Thanks!

1.

=COUNTIF(Range,"=2")

or

=COUNTIF(Range,"2")

but since the next start with 3 I assume you want to include 2, else use the
second formula

2.

=COUNTIF(Range,"=3")-COUNTIF(Range,"5")

3.

=COUNTIF(Range,"=6")-COUNTIF(Range,"8")

and so on



--

Regards,

Peo Sjoblom


"Dawn" wrote in message
...
I need to set up a formula that will count a value if it meets a
particular
criteria within a range.

Count if the number is less than 2
Count if the number is between the range of 3 & 5
Count if the number is between the range of 6 & 8
and so on....

Thanks!

Hi
never heard of this. If you like email me your file and I'll have a
look at it
email: frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany


Matt wrote:
I have a very large spreadsheet where I am using Countif to count the
data in the first column. It works perfectly for the first 10,750
rows and then returns the wrong count for the remaing rows. Example:
12 when the correct count should be 9. I have 575 rows remaining. If
I cut and paste, say 20 rows into another spreadsheet the count is
correct on the new spreadsheet. If I paste all 575 rows into a new
spreadsheet the count is wrong. The spreadsheet is set up as follows.

Column A Column B
NLM =COUNTIF(A:A,A1) result is 3
NLM =COUNTIF(A:A,A2) result is 3
NLM =COUNTIF(A:A,A3) result is 3
WLK =COUNTIF(A:A,A4) result is 2
WLK =COUNTIF(A:A,A5) result is 2

The count is used for a summary on another worksheet in the same
workbook.

Does any of this make sense?

Use the advanced filter to extract a unique list from A
(datafilteradvanced filter, copy to another location,
unique records only), then use that list to refer to your count,

assume that you copy the unique list into D21, use

=COUNTIF(A:A,D2)

copy down along the filtered list.
Why do you count the same value over and over?

--

Regards,

Peo Sjoblom


"Matt" wrote in message
...
I have a very large spreadsheet where I am using Countif to count the data
in the first column. It works perfectly for the first 10,750 rows and then
returns the wrong count for the remaing rows. Example: 12 when the correct
count should be 9. I have 575 rows remaining. If I cut and paste, say 20
rows into another spreadsheet the count is correct on the new spreadsheet.
If I paste all 575 rows into a new spreadsheet the count is wrong. The
spreadsheet is set up as follows.

Column A Column B
NLM =COUNTIF(A:A,A1) result is 3
NLM =COUNTIF(A:A,A2) result is 3
NLM =COUNTIF(A:A,A3) result is 3
WLK =COUNTIF(A:A,A4) result is 2
WLK =COUNTIF(A:A,A5) result is 2

The count is used for a summary on another worksheet in the same workbook.

Does any of this make sense?

Should of course be D2, not D21

--

Regards,

Peo Sjoblom


"Peo Sjoblom" wrote in message
...
Use the advanced filter to extract a unique list from A
(datafilteradvanced filter, copy to another location,
unique records only), then use that list to refer to your count,

assume that you copy the unique list into D21, use

=COUNTIF(A:A,D2)

copy down along the filtered list.
Why do you count the same value over and over?

--

Regards,

Peo Sjoblom


"Matt" wrote in message
...
I have a very large spreadsheet where I am using Countif to count the
data
in the first column. It works perfectly for the first 10,750 rows and then
returns the wrong count for the remaing rows. Example: 12 when the correct
count should be 9. I have 575 rows remaining. If I cut and paste, say 20
rows into another spreadsheet the count is correct on the new spreadsheet.
If I paste all 575 rows into a new spreadsheet the count is wrong. The
spreadsheet is set up as follows.

Column A Column B
NLM =COUNTIF(A:A,A1) result is 3
NLM =COUNTIF(A:A,A2) result is 3
NLM =COUNTIF(A:A,A3) result is 3
WLK =COUNTIF(A:A,A4) result is 2
WLK =COUNTIF(A:A,A5) result is 2

The count is used for a summary on another worksheet in the same
workbook.

Does any of this make sense?

I am counting the same value over and over for the summary sheet. I am using Vlookup on the summary sheet to look up the count on my data sheet. Because of the amount of data and the formatting (this was data downloaded from SAP) it seemed to be the easiest way to obtain the count. Is there another way to do this?

Hi
yes do just that :-)

--
Regards
Frank Kabel
Frankfurt, Germany


Matt wrote:
Frank, the file is approximately 26mb. I can send you a copy of the
problem area if thats ok.

"=?Utf-8?B?TWF0dA==?=" wrote...
I have a very large spreadsheet where I am using Countif to count the data in
the first column. It works perfectly for the first 10,750 rows and then
returns the wrong count for the remaing rows. Example: 12 when the correct
count should be 9. I have 575 rows remaining. If I cut and paste, say 20 rows
into another spreadsheet the count is correct on the new spreadsheet. If I
paste all 575 rows into a new spreadsheet the count is wrong. The spreadsheet
is set up as follows.

Column A Column B
NLM =COUNTIF(A:A,A1) result is 3
NLM =COUNTIF(A:A,A2) result is 3
NLM =COUNTIF(A:A,A3) result is 3
WLK =COUNTIF(A:A,A4) result is 2
WLK =COUNTIF(A:A,A5) result is 2

The count is used for a summary on another worksheet in the same workbook.

If your counts were understated, I'd suspect variable trailing space characters.
However, your count is overstated, so do any of your column A entries contain
either ? or * characters?

--
To top-post is human, to bottom-post and snip is sublime.

Hi
as a follow-up:
Nothing was wrong with the formulas. Just some additional matching
values at the end of the range (which seemed to be sorted but wasn't)


--
Regards
Frank Kabel
Frankfurt, Germany


Frank Kabel wrote:
Hi
yes do just that :-)


Matt wrote:
Frank, the file is approximately 26mb. I can send you a copy of the
problem area if thats ok.