SUMPRODUCT

[Srikanth]

Hello,
I was looking for some help on one of the formula I had used for
generating some report.
Using SUMPRODUCT from various parameters I was getting average of
data
entered in the cells TAT!$E$3:$E$1000. But few of my cells would be
either "blank" or "zero", in which case those cells should not be
considered.
The avarage for these cells (TAT!$E$3:$E$1000) are calculated based on
an
input data in cell B10. and that input data is compared in TAT!$B$3:$B
$1000

My question is, though B10 data appears in TAT!$B$3:$B$1000, if the
corresponding cells (E3 to E1000) is either "blank" or "zero" then
those
cells should be ignored.
For Eg:-
Name Data
a 15
b 25
c 20
a
a 42
a

In the give data though a is appering 4 times, data is appearing only
two times in corresponding cell. So average should be (15+42)/2 AND
NOT (15+42)/4

Assistance would be of great help!!!

This is the formula I have used:-

SUMPRODUCT((TAT!$B$3:$E$1000=Consolidated!$B10)*(TAT!$E$3:$E$1000)*
(TEXT(TAT!$C$3:$C$1000,"mmm")=Consolidated!$B$2)*(YEAR(TAT!$C$3:$C
$1000)=2009))/SUMPRODUCT((TAT!$B$3:$E$1000=Consolidated!$B10)*(TEXT
(TAT!$C$3:$C$1000,"mmm")=Consolidated!$B$2)*(YEAR(TAT!$C$3:$C$1000)
=2009))

Thanks in advance!!!
Srikanth

 

[Abdul]

Hi Shrikanth,

Iif you are looking to get the avarage based on your condition,
assuming that your data is in A1:B6

=AVERAGEIFS(B1:B6,B1:B6,">0",A1:A6,"=a") will give you 28.5

Thanks,

Abdul