Counting Blanks
 

Has Excel always counted blanks under the following condition?:

{=sum((range1=condition1)*(cond2result2))}

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

Counting Blanks
 

Not sure I have understood your query..

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

OR non-array formula
=SUMPRODUCT((range1=condition1)*(range2MIN(result 2)))


--
Jacob


"JPDS" wrote:

Has Excel always counted blanks under the following condition?:

{=sum((range1=condition1)*(cond2result2))}

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

Counting Blanks
 

Hi,
try

=SUMPRODUCT((range1=condition1)+(resul2=con2))


"JPDS" wrote:

Has Excel always counted blanks under the following condition?:

{=sum((range1=condition1)*(cond2result2))}

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

Counting Blanks
 

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?

"Eduardo" wrote:

Hi,
try

=SUMPRODUCT((range1=condition1)+(resul2=con2))


"JPDS" wrote:

Has Excel always counted blanks under the following condition?:

{=sum((range1=condition1)*(cond2result2))}

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

Counting Blanks
 

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

--
Jacob


"Jacob Skaria" wrote:

Not sure I have understood your query..

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

OR non-array formula
=SUMPRODUCT((range1=condition1)*(range2MIN(result 2)))


--
Jacob


"JPDS" wrote:

Has Excel always counted blanks under the following condition?:

{=sum((range1=condition1)*(cond2result2))}

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

Counting Blanks
 

Is there a rationale for this? Why does this not occur in non-array formula?

Thanks Jacob

"Jacob Skaria" wrote:

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

--
Jacob


"Jacob Skaria" wrote:

Not sure I have understood your query..

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

OR non-array formula
=SUMPRODUCT((range1=condition1)*(range2MIN(result 2)))


--
Jacob


"JPDS" wrote:

Has Excel always counted blanks under the following condition?:

{=sum((range1=condition1)*(cond2result2))}

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

Counting Blanks
 

Hi,
the sumproduct formula is

=sumproduct(--(a1:a6<100),--(a1:a6<""),--(b1:b6="a"))

"JPDS" wrote:

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?

"Eduardo" wrote:

Hi,
try

=SUMPRODUCT((range1=condition1)+(resul2=con2))


"JPDS" wrote:

Has Excel always counted blanks under the following condition?:

{=sum((range1=condition1)*(cond2result2))}

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