I am trying to average numbers contained on several different worksheets in one workbook. I need to not include those that are zeros.
I tried the following statement, but it does not like my countif statement. Is there any way for me to do this without moving all of the information to one worksheet?
=SUM(Sheet1!A1,Sheet2!A1,Sheet3!A1)/COUNTIF(Sheet1!A1,Sheet2!A1,Sheet3!A1,">0")
Try:
=SUM(Sheet1:Sheet3!A1)/SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!A1"),">0"))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
That worked beautifully! Thank you so much!
You're welcome.
Please remember to mark your thread as Solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Anita Donnelly 
Register To Reply
Bookmarks