Selective comparison to count

This is a follow up to a previously answered question.

{=SUM(($BH$3:$BH$507>=BQ3:BQ507)*1)}

I am using the above formula to evaluate and compare two columns of numbers.

Column range BQ3:BQ507 has the ability to contain 0.  Is it possible to use the above formula, or something similar, to evaluate only those cells which have a number greater then 0?

Thanks for your input.
MCaliebeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
{=SUM((BQ3:BQ507<>0)*($BH$3:$BH$507>=BQ3:BQ507)*1)}
0
 
Saqib Husain, SyedEngineerCommented:
I am not sure but I think you can get rid of *1 for this case

{=SUM((BQ3:BQ507<>0)*($BH$3:$BH$507>=BQ3:BQ507))}
0
 
MCaliebeAuthor Commented:
I didn't try the other example...the initial worked fine.

Now, I just need to get schooled on what an "Array" formula is and I need to use them.  Any suggetions?
0
 
Saqib Husain, SyedEngineerCommented:
To understand this start with a small range say

{=SUM((BQ3:BQ7<>0)*($BH$3:$BH$7>=BQ3:BQ7))}

then

Formulas > Formula auditing > Evaluate formula

Step through this by clicking the Evaluate button and see how the formula works.
0
 
MCaliebeAuthor Commented:
Thanks!  I am following this.  In addition, I found a good article here http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx on how and why we use array formulas.
0
All Courses

From novice to tech pro — start learning today.