Average if not equal to 0 and equal to text string in other cell

[flagen]

Hi all,

I am attempting to create a table of averages based on two criteria. I want to get an average of costs that are not equal to 0 and fit into a certain category. I have tried both averageifs and sumifs/countifs functions. Here are the two formulas I have most recently tried. It says there is an error in my formula in both of them.

=AVERAGEIFS(O$3:O2196,LEFT($C$3:$C2196,10),$K2206,O$3:O2196,"<>0")

=SUMIFS(O$3:O2196,LEFT($C$3:$C2196,10),$K2206,O$3:O2196,"<>0"))/COUNTIFS(LEFT($C$3:$C2196,10),$K2206,O$3:O2196,"<>0"))

I am averaging everything in column "O" that doesn't equal 0 and fits within one of the categories in column "C" based on the text string given in column "K". This is a column of averages I placed at the bottom of my data. Any help is appreciated, thank you!

 

[Special-K99]

I don't think you can have LEFT() in there

SUMPRODUCT should do it, thinking...

 

[Special-K99]

There's probably a shorter way but till someone comes along with it try...

=SUMPRODUCT((LEFT($C$3:$C$2196,10)=$K2206)*($O$3:$O2196<>0)*($O$3:$O2196))/SUMPRODUCT(--(LEFT($C$3:$C$2196,10)=$K2206)*($O$3:$O2196<>0))

 

[flagen]

That worked! Can you explain what that does to get to the answer though? I would never have thought of SUMPRODUCT for this.

Thank you Special-K.

 

[flagen]

Additionally, if I wanted to do the same thing, would it be possible to change the first criteria to LEFT and RIGHT string filters? As in, check the left # of characters and right # of characters against "K2206". Example using my old equation:

=AVERAGEIFS(O$3:O2196,AND(LEFT($C$3:$C2196,5),RIGHT($C$3:$C2196,3)),$K2206,O$3:O2196,"<>0")