Re: Array Formula Help -IF / Else clause?

Tech-Archive recommends: Speed Up your PC by fixing your registry

The second IF is not part of the "else", it's part of the "Then".

So if it's a ford, then if it's a focus, put 1. Very close to AND, huh?

Another way to do this same kind of thing:

=sumproduct(--(a1:a100="ford"),--(b1:b100="focus"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Jay wrote:

Hi,

I'm just getting to grips with Array formulae, and one of the formula I
use most frequently is to count combinations of values over two arrays:
For example:

=SUM(IF(A1:A100="Ford",IF(B1:B100="Focus"1,0)))

To give a count of all the Ford Focus in a 2 column list/array (Make &
Model in columns A and B respectively)

I understand how it works. Creates (and sums) an array of 1s for every
combination where there is Ford & Focus.

But what I don't understand is how the second IF fits in.

Isn't the second IF basically the ELSE clause of the first IF? But
doesn't the formula almost work as if it's an AND? SUM the 1s IF A=Ford
*AND* B=Focus.

I don't understand how this fits my usual ubderstanding of the ELSE
clause of an IF statement. And I think it's this stumbling block that's
stopping me making greater use of such functions.

If anyone could enlighten me I'd really appreciate it?

TIA,

Jason

--

Dave Peterson
.

Quantcast