Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 26th 05, 09:45 PM
Renee - California
 
Posts: n/a
Default Countif for specific cells rather than a range ????

Is this possible? I've created a 25 page worksheet to analyze cases and
dollars for 24 stores. The 25 pages are identical; the first page is a
summary of the region (the other 24 pages or stores).

I'm trying to get the averages on the summary page. So I've summarized by
adding each stores cell reference for whatever item I'm looking at. For
example
=N13+X13+AB13+AH13 etc.
and now to get the average, I want to divide by the number of stores that
are actually carrying that item. So I thought I could do a countif, counting
if the cells in that range are greater than zero. I can't seem to make this
work though.

What should this formula look like... is there an easier way to do this??



  #2   Report Post  
Old May 26th 05, 10:21 PM
Ron Rosenfeld
 
Posts: n/a
Default

On Thu, 26 May 2005 13:45:35 -0700, Renee - California
wrote:

Is this possible? I've created a 25 page worksheet to analyze cases and
dollars for 24 stores. The 25 pages are identical; the first page is a
summary of the region (the other 24 pages or stores).

I'm trying to get the averages on the summary page. So I've summarized by
adding each stores cell reference for whatever item I'm looking at. For
example
=N13+X13+AB13+AH13 etc.
and now to get the average, I want to divide by the number of stores that
are actually carrying that item. So I thought I could do a countif, counting
if the cells in that range are greater than zero. I can't seem to make this
work though.

What should this formula look like... is there an easier way to do this??


COUNTIF is not one of the functions that will function with 3D references.

Option 1: Bring the stores cell reference for each whatever item to some out
of the way spot on your worksheet, or on another single worksheet; then use the
COUNTIF formula to refer to the cells on that sheet.

Option 2: Download Laurent Longre's free morefunc.xll add-in from
http://xcell05.free.fr/ and use his COUNTIF.3D function which allows 3D
references.


--ron
  #4   Report Post  
Old May 27th 05, 03:17 PM
Ron Rosenfeld
 
Posts: n/a
Default

On Thu, 26 May 2005 13:45:35 -0700, Renee - California
wrote:

Is this possible? I've created a 25 page worksheet to analyze cases and
dollars for 24 stores. The 25 pages are identical; the first page is a
summary of the region (the other 24 pages or stores).

I'm trying to get the averages on the summary page. So I've summarized by
adding each stores cell reference for whatever item I'm looking at. For
example
=N13+X13+AB13+AH13 etc.
and now to get the average, I want to divide by the number of stores that
are actually carrying that item. So I thought I could do a countif, counting
if the cells in that range are greater than zero. I can't seem to make this
work though.

What should this formula look like... is there an easier way to do this??


I may have misunderstood your first post.

I believe that in order to use COUNTIF, or an AVERAGE(IF(rng0),rng)) array
function, you will need to copy those results into a contiguous range.

I do not believe that either COUNTIF or array functions can accept
non-contiguous ranges.


--ron
  #5   Report Post  
Old May 27th 05, 06:23 PM
Harlan Grove
 
Posts: n/a
Default

Ron Rosenfeld wrote...
....
I do not believe that either COUNTIF or array functions can accept
non-contiguous ranges.


If you get tricky enough, you can come close.

=SUMPRODUCT(COUNTIF(OFFSET(N13,0,{0,10,14,20}),wha tever))

But I don't think this is what the OP is after. The OP would be vastly
better off using a database rather than a spreadsheet to calculate
conditional averages over several stores.



  #6   Report Post  
Old May 27th 05, 07:09 PM
Ron Rosenfeld
 
Posts: n/a
Default

On 27 May 2005 10:23:27 -0700, "Harlan Grove" wrote:

Ron Rosenfeld wrote...
...
I do not believe that either COUNTIF or array functions can accept
non-contiguous ranges.


If you get tricky enough, you can come close.

=SUMPRODUCT(COUNTIF(OFFSET(N13,0,{0,10,14,20}),wh atever))

But I don't think this is what the OP is after. The OP would be vastly
better off using a database rather than a spreadsheet to calculate
conditional averages over several stores.


That is a neat trick!


--ron


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
Counting empty cells within a range of cells Rosehill - ExcelForums.com New Users to Excel 2 May 2nd 05 08:53 AM
Counting empty cells within a range of cells Rosehill - ExcelForums.com New Users to Excel 0 April 7th 05 12:47 AM
How can I get a count of cells in a range that are shaded a certa. Slinkeysgirl Excel Worksheet Functions 3 April 1st 05 03:53 PM
Applying formula to only NON-EMPTY cells in range Tasi Excel Discussion (Misc queries) 5 March 29th 05 10:48 PM
How can I dynamically eliminate blank cells in a given range in E. Scott Steele Excel Worksheet Functions 6 December 17th 04 03:23 AM


All times are GMT +1. The time now is 04:14 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017