Is there a way to get an average using count for SumProduct?

[Pmayhew]

Hi All,
I have a formula which does a good job of providing a annual rent for a tenant. The only problem is that if the year has more than 2 rent numbers, it sums them. I cannot figure out how to use count to get the average for the year. Any assistance would be greatly appreciated!
-Trish
=IF(W14="","",SUMPRODUCT(--($N$17:$N$1000=$V14),--(RIGHT($R$17:$R$1000,4)=$W$8),$S$17:$S$1000))

V14 = unique identifier on summary table
N17 = unique identifier for tenant in massive list. Each tenant has one uniqure number but it is listed several times
R17 - starts the list of dates:
Jan-2011
Dec-2011
etc several for each tenant
W8 = year 2011
S17 = are the coresponding dollar amounts for the corresponding date in R17

 

[T. Valko]

Hi All,
I have a formula which does a good job of providing a annual rent for a tenant. The only problem is that if the year has more than 2 rent numbers, it sums them. I cannot figure out how to use count to get the average for the year. Any assistance would be greatly appreciated!
-Trish
=IF(W14="","",SUMPRODUCT(--($N$17:$N$1000=$V14),--(RIGHT($R$17:$R$1000,4)=$W$8),$S$17:$S$1000))

V14 = unique identifier on summary table
N17 = unique identifier for tenant in massive list. Each tenant has one uniqure number but it is listed several times
R17 - starts the list of dates:
Jan-2011
Dec-2011
etc several for each tenant
W8 = year 2011
S17 = are the coresponding dollar amounts for the corresponding date in R17

What version of Excel are you using?

R17 - starts the list of dates:
Jan-2011
Dec-2011

Are these TEXT strings or true Excel dates formatted to display as mmm-yyyy ?

 

[Pmayhew]

Hi,
I am using 2002 and it is actually Text (an Argus data dump). Hope that helps.

 

[T. Valko]

Hi,
I am using 2002 and it is actually Text (an Argus data dump). Hope that helps.

Ok, try this array formula**:

=IF(W14="","",AVERAGE(IF($N$17:$N$1000=$V14,IF(RIGHT($R$17:$R$1000,4)=$W$8&"",$S$17:$S$1000))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Pmayhew]

That seems to have worked. Thanks a million!

 

[T. Valko]

That seems to have worked. Thanks a million!

You're welcome. Thanks for the feedback!