Average If excel 2003

[proctk]

Hi how could I change this formula to do average if

=SUMPRODUCT(SUMIF(INDIRECT("'"&T6:T57&"'!B:B"),G3,INDIRECT("'"&T6:T57&"'!P:P")))

If tried a couple things and googled without luck.

Thank you!

 

[T. Valko]

Hi how could I change this formula to do average if

=SUMPRODUCT(SUMIF(INDIRECT("'"&T6:T57&"'!B:B"),G3,INDIRECT("'"&T6:T57&"'!P:P")))

If tried a couple things and googled without luck.

Thank you!

Like this...

=SUMPRODUCT(SUMIF(INDIRECT("'"&T6:T57&"'!B:B"),G3,INDIRECT("'"&T6:T57&"'!P:P")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&T6:T57&"'!B:B"),G3))

 

[proctk]

Thank you for this. It does not do quite what I want it to. The spreadsheet has 52 tabs, one for each day of the week. As the week pasts I fill in the template. So the spreadsheet will always countif 52. I only want it to use the weeks that I have completed the template.

Example date squence

05-Nov-10
12-Nov-10
19-Nov-10
26-Nov-10
03-Dec-10
10-Dec-10


Thank you once again!

 

[T. Valko]

Thank you for this. It does not do quite what I want it to. The spreadsheet has 52 tabs, one for each day of the week. As the week pasts I fill in the template. So the spreadsheet will always countif 52. I only want it to use the weeks that I have completed the template.

Example date squence

05-Nov-10
12-Nov-10
19-Nov-10
26-Nov-10
03-Dec-10
10-Dec-10


Thank you once again!

Sorry, I don't understand.

 

[proctk]

I'll try again.

The spreadsheet has 52 tabs and each tab has the information completed in column B:B which is used in the countif formula. So the formula you helped with will aways count 52.

I only want to count the spreadsheets where say values are found in column C. of is c is not blank then count.

Hope this helps.

 

[Jonmo1]

Gotta agree with Biff

If this gives a correct SUM
=SUMPRODUCT(SUMIF(INDIRECT("'"&T6:T57&"'!B:B"),G3,INDIRECT("'"&T6:T57&"'!P:P")))

Then this will give you the count
=SUMPRODUCT(COUNTIF(INDIRECT("'"&T6:T57&"'!B:B"),G3))


Average is defined as SUM devided by COUNT
So Biff's formula will return the average.

 

[proctk]

okay

who can we change this so it looks for a value not = to 0

i.e.
=SUMPRODUCT(COUNTIF(INDIRECT("'"&T6:T57&"'!C:C"<>0),G3))

 

[T. Valko]

okay

who can we change this so it looks for a value not = to 0

i.e.
=SUMPRODUCT(COUNTIF(INDIRECT("'"&T6:T57&"'!C:C"<>0),G3))

Like this...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&T6:T57&"'!C:C"),"<>0"))