Counting Blanks

  • Thread starter Thread starter JPDS
  • Start date Start date
J

JPDS

Has Excel always counted blanks under the following condition?:

{=sum((range1=condition1)*(cond2>result2))}

where result2 could contain blanks to allow for errors in a calculated result?

I know that I can amend the formula so that blanks are excluded but I never
expected blanks to be counted.

Thanks
 
Not sure I have understood your query..

What about using MIN() for the array
{=SUM((range1=condition1)*(range2>MIN(result2)))}

OR non-array formula
=SUMPRODUCT((range1=condition1)*(range2>MIN(result2)))
 
Gives a totally different answer.

Imagine a1:a6 = 10,20,30,40,blank,100
b1:b6 = a,a,a,a,a,a

=SUMPRODUCT((A1:A6<100)*(B1:B6="A")) Result=5 - WRONG
{=SUM((A1:A6<100)*(B1:B6="A")) Result=5 - WRONG
=COUNTIF(A1:A6,"<100") Result=4 - CORRECT

The sumproduct and SUM(Array) formula count the blank. Does that make sense?
 
Seeing your response; I think I have totally misunderstood your
question...While using array formulas the blanks are treated as 0..

=SUM((A1:A6=0)*(B1:B6="A")) will return 1
 
Back
Top