# Re: Help! How do I ignore blanks cells in an array?

joeu2004
Guest
Posts: n/a

 28th Jul 2012
"jblanks7" <(E-Mail Removed)> wrote:
Subject: Help! How do I ignore blanks cells in an array?
> =SUM((G38:U38>=(AC38-50))*(G38:U38<=(AC38-10)))
>
> This shows a 16 week season (G38:U38 represents each week).
> AC38 represents the player's season average. The issue is
> that this player only played in 8 of the team's 16 weeks.
> The average is correct, but the array is counting the 8
> weeks that he didn't play as "0."

I think you want the following array-entered formula:

=SUM((G38:U38>=AC38-50)*(G38:U38<=AC38-10)*(G38:U38<>""))

I think you know that "array-entered" means press ctrl+shift+Enter instead
of just Enter.

I find that array-entered formulas are often difficult to maintain. And
sometimes they __appear__ to work (i.e. there is no error) when we make the
mistake of simply pressing Enter; but in fact, the result is incorrect.

For that reason, I prefer to use SUMPRODUCT for such formulas.
Normally-enter (just press Enter) the following formula:

=SUMPRODUCT((G38:U38>=AC38-50)*(G38:U38<=AC38-10)*(G38:U38<>""))

Also, if you are using Excel 2007 or later and you do not need Excel 2003
compatibility (e.g. to share the file with others who might have older Excel
versions), you could use COUNTIFS, to wit:

=COUNTIFS(G38:U38,">="&AC38-50,G38:U38,"<="&AC38-10,G38:U38,"<>")

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post =?Utf-8?B?TmF0Q2hhdA==?= Microsoft Excel Misc 5 26th Feb 2007 06:01 AM =?Utf-8?B?anVzdGFndXlmcm9ta3k=?= Microsoft Excel Programming 5 3rd Sep 2006 11:23 PM =?Utf-8?B?anVzdGFndXlmcm9ta3k=?= Microsoft Excel Worksheet Functions 1 3rd Sep 2006 07:56 PM =?Utf-8?B?Z3Nyb3Npbg==?= Microsoft Excel Misc 0 22nd Feb 2005 04:33 AM Dennis Microsoft Excel Misc 8 9th Apr 2004 10:54 PM

Features