count if / average only when a value is present

**Rwilliams_09** · 12-17-2012, 02:09 PM

Hi All,

Trying to count if only when there is a value present on the SP page. I'm trying to show the average of the numbers (will weight afterwards) onto the Summary page for each qtr and month.

This is what I have now but can't seem to figure out why it doesn't work.

=IF(COUNT(SP!C3:E5),AVERAGE(SP!C3:E5),"")

Thanks for your help.

**Ace_XL** · 12-17-2012, 02:20 PM

try this array formula..

Please Login or Register  to view this content.

confirm with Ctrl+Shift+Enter and not just Enter

Adjust ranges accordingly

**Ron Coderre** · 12-17-2012, 02:26 PM

Try this ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)

Please Login or Register  to view this content.

Does that helP?

**Rwilliams_09** · 12-17-2012, 05:38 PM

That is close but I need it to return a blank or not an error if the support page (SP) does not have a value or is an error.

**Ace_XL** · 12-17-2012, 05:44 PM

Try this..

Please Login or Register  to view this content.

or edited version of Ron's formula

Please Login or Register  to view this content.

count if / average only when a value is present

LinkBack

Thread Tools

Rate This Thread

Display

count if / average only when a value is present

Re: count if / average only when a value is present

Re: count if / average only when a value is present

Re: count if / average only when a value is present

Re: count if / average only when a value is present

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions