Home 
Search 
Today's Posts 
#1




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




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 addin from http://xcell05.free.fr/ and use his COUNTIF.3D function which allows 3D references. ron 
#3




It couldn't be as simple as
=sum(N13+X13+AB13+AH13)/4 ??? Probably not. So you would need to provide more info. Perhaps an array =average(if ??  Don Guillett SalesAid Software "Renee  California" wrote in message ... 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?? 
#4




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 noncontiguous ranges. ron 
#5




Ron Rosenfeld wrote...
.... I do not believe that either COUNTIF or array functions can accept noncontiguous 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




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 noncontiguous 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 
Display Modes  


Similar Threads  
Thread  Forum  
Counting empty cells within a range of cells  New Users to Excel  
Counting empty cells within a range of cells  New Users to Excel  
How can I get a count of cells in a range that are shaded a certa.  Excel Worksheet Functions  
Applying formula to only NONEMPTY cells in range  Excel Discussion (Misc queries)  
How can I dynamically eliminate blank cells in a given range in E.  Excel Worksheet Functions 