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 blan or "zero", in which case those cells should not be
considered.
The avarage for these cells (TAT!$E$3:$E$1000) are done based on an
input name in cell B10. and that name 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 to be ignored.
Assistance would be of great help!!!

This is the formula I have used:-

SUMPRODUCT((TAT!$B$3:$E$1000=Consolidated!$B10)*(T AT!$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)*(T EXT
(TAT!$C$3:$C$1000,"mmm")=Consolidated!$B$2)*(YEAR( TAT!$C$3:$C$1000)
=2009))