Solved

# Selective comparison to count

Posted on 2012-03-19
305 Views

{=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?

0
Question by:MCaliebe
• 3
• 2

LVL 43

Accepted Solution

Saqib Husain, Syed earned 250 total points
{=SUM((BQ3:BQ507<>0)*(\$BH\$3:\$BH\$507>=BQ3:BQ507)*1)}
0

LVL 43

Expert Comment

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

Author Closing Comment

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

LVL 43

Expert Comment

{=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

Author Comment

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

## Featured Post

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…