MrExcel.com - Your One Stop for Excel Tips & Solutions

CSE Question "

Posted by Bobby on October 17, 2001 10:15 AM

I can get =SUM(If(A1:A5=A6,If(b1:b5=b6,1,0),0)) I cant get =SUM(If(A1:A5=A6,If(b1:b5=b6,1,0),0)) to Work. Is there a way to get around this.

Posted by bobby on October 17, 2001 10:17 AM

Re: CSE Question

Sorry I forgot to change the Second Formula it the "<" that is giving me the Trouble. I can get =SUM(If(A1:A5=A6,If(b1:b5=b6,1,0),0)) I cant get =SUM(If(A1:A5=A6,If(b1:b5<b6,1,0),0)) to Work. Is there a way to get around this.


Posted by bobby on October 17, 2001 10:18 AM

Re: CSE Question

Sorry I forgot to change the Second Formula it the "<" that is giving me the Trouble. I can get =SUM(If(A1:A5=A6,If(b1:b5=b6,1,0),0)) I cant get =SUM(If(A1:A5=A6,If(b1:b5<b6,1,0),0)) to Work. Is there a way to get around this.


Posted by Mark W. on October 17, 2001 10:21 AM

Re: CSE Question

{=SUM((A1:A5 = A6)*(B1:B5 < B6))}


Posted by Aladin Akyurek on October 17, 2001 10:22 AM

Try instead:

=SUM((A1:A5=A6)*(B1:B5=B6))

which must be array-entered (by CSE).

A non-array version that does same thing is:

=SUMPRODUCT((A1:A5=A6)*(B1:B5=B6))

Aladin

Posted by Aladin Akyurek on October 17, 2001 10:25 AM

Replace the 2nd "=" with "<". (NT)

Posted by IML on October 17, 2001 10:27 AM

Re: CSE Question

Copying the formulas with the less sign often give problems. Sometime it helps to put spaces around it. Anyway, you should just be able to edit your formula, and hit control shift enter for the right results.
If you want to count how many items equal to A6 in A1-A5 and less than or equal to b6 in B1-b5, you could try
=SUM(IF(A1:A5=A6,IF(B1:B5 &LT; =B6,1,0),0))

or

=SUM((A1:A5=A6)*(B1:B5 &LT; =B6))

or without having to use CSE
=SUMPRODUCT((A1:A5=A6)*(B1:B5&LT;=B6))

Posted by IML on October 17, 2001 10:28 AM

slow slow slow, sorry (nt)

Posted by Bobby on October 17, 2001 10:56 AM

Re: CSE Question (The Formula Works but I have to Double Click in Each Cell)

Thanks, This Worked but I have to DoubleClick on the Each Cell before it will count it in the Number. I tried to calculate cells (F9) No luck. Is there any trick to getting this these cells recognized without clicking in each cell?


Posted by Mark W. on October 17, 2001 11:02 AM

Re: CSE Question (The Formula Works but I have to Double Click in Each Cell)

It sounds like A6 and/or B6 have a different data
type than the comparison ranges (A1:A5, B1:B6).
Keep in mind that 2 does not equal "2"! Make
sure that you're comparing numbers to numbers or
text to text. Thanks, This Worked but I have to DoubleClick on the Each Cell before it will count it in the Number. I tried to calculate cells (F9) No luck. Is there any trick to getting this these cells recognized without clicking in each cell?

Posted by Aladin Akyurek on October 17, 2001 11:15 AM

Re: CSE Question (The Formula Works but I have to Double Click in Each Cell)

Did you check "Calculation" mode? Set it to Automatic if it is not case. It sounds like A6 and/or B6 have a different data

Comparing text formatted numbers with "true" numbers shouldn't hinder an array formula with boolean terms.

Aladin

Posted by Mark W. on October 17, 2001 11:25 AM

Re: CSE Question (The Formula Works but I have to Double Click in Each Cell)

> Comparing text formatted numbers with "true"
> numbers shouldn't hinder an array formula
> with boolean terms.

Aladin, what do you mean by this? What I was
suggesting is that {"1";"2";"3";"4";"5"}=5 will
yield {FALSE;FALSE;FALSE;FALSE;FALSE}. Did you check "Calculation" mode? Set it to Automatic if it is not case. : It sounds like A6 and/or B6 have a different data

Posted by IML on October 17, 2001 11:53 AM

Re: CSE Question (The Formula Works but I have to Double Click in Each Cell)

Two quick cures if the problem is type mis-matches as Mark suggested.
1) If you have static data, highlight the data, go to data - text to columns and hit finish.
2) If the data is from a formula such as a vlookup, just add "+0" to the end of it. Thanks, This Worked but I have to DoubleClick on the Each Cell before it will count it in the Number. I tried to calculate cells (F9) No luck. Is there any trick to getting this these cells recognized without clicking in each cell?

Posted by Aladin Akyurek on October 17, 2001 2:00 PM

Text Formatted Numbers

I should have qualified what I said as type mismatch is always a source of trouble. I had the following situation in mind:

Suppose you have 1, 2, 3 in A1:A3.

Format A1:A3 as Text.

Apply: =SUMPRODUCT(A1:A3) & note the result. [1]

Now change 1 in A1 to 7 & note the result. [2]

Format A5:A7 as Text.

Put in this range 4, 2, 3.

Apply: =SUMPRODUCT(A5:A7). [3]

The diff between [1] and [3] is disturbing. I suppose this was the source of my unqualified remark.


By the way, Ian has already covered how to tackle the problem of type mismatch. Another quick test would be (concerning the case at hand) applying:

=SUM(A1:A6)=SUMPRODUCT(A1:A6+0) and

=SUM(B1:B6)=SUMPRODUCT(B1:B6+0)

Aladin

: