Array Formula Help -IF / Else clause?

[Jay]

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

 

[Peo Sjoblom]

No, if both conditions are true return 1, if not it will be zero.
What you basically do is

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

so it will be TRUE or FALSE and if there are 2 TRUE in the same row it will
retrun 1 when multiplied so it may look like

{0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0
etc.

and then SUM will sum all the 1s

you can write it as this and enter it normally

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

or if you use 2007 and the new function COUNTIFS

=COUNTIFS(A1:A100,"Ford",B1:B100,"Focus")

I only use sum array formulas when there is no other choice

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)

 

[Dave Peterson]

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

 

[Ragdyer]

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"))

 

[Jay]

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"))

THanks for this RD, I wasn't aware of the use of SUMPRODUCT as a
multiple argument count function. I agree that it is a lot simpler.

Thanks again,

Jason