Re: Array Formula Help -IF / Else clause?

Besides the fact that you left a comma out of your posted formula, why not
get in the habit of using the non-array SumProduct() function, which is more
straight forward for comprehension:

=SUMPRODUCT((A1:A100="Ford")*(B1:B100="Focus"))

And if you have additional criteria to include, simply add another argument:

=SUMPRODUCT((A1:A100="Ford")*(B1:B100="Focus")*(C1:C100="Red"))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jay" <zeugma@xxxxxxxxxxxxxx> wrote in message
news:uMlwDYyvGHA.1372@xxxxxxxxxxxxxxxxxxxxxxx
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


.