Average while using SumProduct

S

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 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)*(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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMPRODUCT 1
Sumproduct? 3
If Condition 4
If Condition 0
Is there a more efficient way to write this SUMPRODUCT 4
Variable Sumproduct Range 6
sumproduct using criteria from 2 worksheets 1
sumproduct using criteria from multiple columns in a separate wksh 1

Top