Average based on two rows and two criterea?

[exceller]

Hi All,

I'm new on this forum, so if a solution to this question has been give
already please let me know.

Here's the situation:

In the first row of data a number of reoccurring column labels appea
with a subtotal:

A - B - C - D - Total - A - B - C - D - Total - A - etc... ...- Gran
Total

The sum of the "Total" columns per row is a simple sumif function whic
works fine.

Now I want to count all the non-zero values in the "Total" columns. I
sounds so simple, but I can't figure out a formula to do it..

So the count is based on the label row and an underlying data row wit
each a criterea in the same column.

Is this a VB case and if so, could anyone give me a lead to get on?

I've been trying all kinds of combined formulas with LOOKUPs, OFFSETs
ANDs, IFs, COUNTIFs and what not...

With this count an average would be simple to calculate.

Many thanks!

Excelle

 

[Guest]

Caution: I have a tendency to go to San Fran by way of
Miami. With that disclaimer...

In FIRST column to right of Grand Total, enter: =IF
(e1<>0,1,0)
NEXT column to right, enter: =IF(j1<>0,1,0)
NEXT column to right, enter: =IF(o1<>0,1,0)
CONTINUE this pattern until all 'sub-totals' are sample by
similar IF statement.
NEXT column to right, enter: =SUM(?1:??1) [This will sum
all IF statement values for the row!]
COPY the above entries into every data row of your
spreadsheet. [Same columns]
Sum all the =SUM's to get total number of non-zero 'Total'
cells.

Perhaps I did not understand your goal. If this is not
what you are looking for, maybe this will spark another
idea.

One of the gurus monitoring this list may put you onto a
quicker VBA or Excel solution. For those of us who do not
have the VB background, we have to find other ways to do
things.

GL

gary b
(e-mail address removed)

 

[exceller]

Caution: I have a tendency to go to San Fran by way of
Miami. With that disclaimer...

Haha, I do the same thing...

What I have used in the mean time as the non-"total" fields are not
populated by zeros is a count of all "total" labels minus the count of
the zeros in the rows below.

Sample plus error trapment:

=IF(ISERROR(SUMIF($F$5:$GC$42,"Total",F7:HN7)/(COUNTIF($F$5:$GC$5,"total")-(COUNTIF(F7:GC7,0)))-1),"-",SUMIF($F$5:$GC$42,"Total",F7:HN7)/(COUNTIF($F$5:$GC$5,"total")-(COUNTIF(F7:GC7,0))))

The length of this spreadsheet is constantly changing.

You have given me a good idea though, every new idea is welcome!

 

[exceller]

I have the solution:

Too much to explain, so check the attached file.

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=380918

 

[akyurek]

If you insist on having a formula without auxiliary computations...

=AVERAGE(IF((MOD(COLUMN($A2:$O2)-CELL("Col",$A2)+1,5)=0)*$A2:$O2,$A2:$O2))

which must be confirmed with control+shift+enter instead of just with enter.