Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default =COUNTIF & how to make it apply to consecutive cells



I'm a fairly new excel user and need some advice on how to count the number
of cells with a specific value in a column. I know that you can use the
=COUNTIF(range, "criteria") ifunction in this manner, but I have so many
values that I want to automate this. Is there anyway that I can keep the
range constant (i.e. always E2:E597 instead of shifting from E3:E598 as I
proceed down the column), but change the criteria (i.e.
=COUNTIF(E2:E597,"D2")) where D2 is the value of cell D2, not the actual
value "D2"?
--
thanks for the advice!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default =COUNTIF & how to make it apply to consecutive cells

=COUNTIF($E$2:$E$597,D2)


"gretchen" wrote:



I'm a fairly new excel user and need some advice on how to count the number
of cells with a specific value in a column. I know that you can use the
=COUNTIF(range, "criteria") ifunction in this manner, but I have so many
values that I want to automate this. Is there anyway that I can keep the
range constant (i.e. always E2:E597 instead of shifting from E3:E598 as I
proceed down the column), but change the criteria (i.e.
=COUNTIF(E2:E597,"D2")) where D2 is the value of cell D2, not the actual
value "D2"?
--
thanks for the advice!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 169
Default =COUNTIF & how to make it apply to consecutive cells

Whenever you specific a range or a cell, you can simply hit F4 to scroll
through the absolute and relative reference options. The first press of F4
will make the reference absolute (e.g., with both $ signs)
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Teethless mama" wrote:

=COUNTIF($E$2:$E$597,D2)


"gretchen" wrote:



I'm a fairly new excel user and need some advice on how to count the number
of cells with a specific value in a column. I know that you can use the
=COUNTIF(range, "criteria") ifunction in this manner, but I have so many
values that I want to automate this. Is there anyway that I can keep the
range constant (i.e. always E2:E597 instead of shifting from E3:E598 as I
proceed down the column), but change the criteria (i.e.
=COUNTIF(E2:E597,"D2")) where D2 is the value of cell D2, not the actual
value "D2"?
--
thanks for the advice!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Countif non consecutive columns shamor New Users to Excel 8 May 31st 08 09:14 PM
How to apply countif? Eric Excel Discussion (Misc queries) 3 October 26th 07 07:01 AM
Using Countif to Count Consecutive Relative Increases in Numbers SteveC Excel Discussion (Misc queries) 16 September 27th 06 04:55 PM
Countif in non-consecutive cells michaelberrier Excel Discussion (Misc queries) 4 June 24th 06 10:43 PM
sumif/countif on non-consecutive colums - array? Gerald Koch Excel Worksheet Functions 2 November 29th 04 06:21 PM


All times are GMT +1. The time now is 09:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"