excel if

[neilpateluk]

Hi,

I have a countif that looks like :

=COUNTIF(L4:L48, "Yes")/(COUNTA(L4:L48)-COUNTIF(L4:L48, "n/a"))

Basically, this works out the percentages of Yes compared to No.

What I need now is to add an extra layer to this formula that will only count the yes' where the value in the F column of that row = 0.

Any idea? Every IF statement I try comes out with #Value??

[deadlyduck]

If you're aiming to count the L-column "Yes" values which also have a corresponding F-column 0 value, this will do it:

Please Login or Register  to view this content.

[Cheeky Charlie]

If you have an even number of matrices in a sumproduct array formula, you can single negate them:
=SUMPRODUCT(-(L4:L48="yes"),-(F4:F48=0))
(-1*-1=1*1)

More significantly, if you have more than one matrix in a sumproduct array formula you can multiply them, eliminating the need for any coercion:
=SUMPRODUCT((L4:L48="yes")*(F4:F48=0))

CC

Neil, if you thought about what you have asked, in order to title your thread better, you would have found numerous examples on this website of 'if with multiple criteria'

[DonkeyOte]

More significantly, if you have more than one matrix in a sumproduct array formula you can multiply them, eliminating the need for any coercion:
=SUMPRODUCT((L4:L48="yes")*(F4:F48=0))

CC, you're not removing coercion by multiplying the arrays, the multiplication is doing the coercion, moreover it is accepted that coercion by multiplication is slower than by unary operator.

I would agree that single operator is faster still however as you infer yourself it is less robust from an end users perspective thus is rarely advised the performance gains from using a single operator rarely warrant the approach and subsequent risk.

The other more pertinent advantage of unary operators over multiplication concerns the handling of the summation range(s) (though not relevant in this instance), if the summation range contains non-numerics Sumproduct by * will generate errors whereas Sumproduct by -- will not, eg:

=SUMPRODUCT({"a","a"}="a")*({"a",10}) -> #VALUE!

whereas

=SUMPRODUCT(--({"a","a"}="a"),{"a",10}) -> 10

the non-numeric will be ignored....

that said * must be used to handle dimensions of different sizes... ie

=SUMPRODUCT(--(A1:A10="a"),--(B1:D10))

will not work given 10x1 x 10x3 whereas the below would work
(assuming of course B1:D3 contain numerics)

=SUMPRODUCT((A1:A3="a")*(B1:D3))

[Cheeky Charlie]

We really ought to put this kind of dissection somewhere it doesn't get buried so quickly.