Replies

Agree, in particular when a multiconditional summing is required. But, if the formula needs to be copied to a multitude of cells, the speed advantage decreases steadily for one needs to use the table method which boils down to an array formula entered in multiple cells. See my contrib in:

http://www.mrexcel.com/board2/viewtopic.php?t=60895%5B/url%5D

It’s faster.

=LOOKUP(9.99999999999999E+307,D:D)

Hans,

There is no need for ISNA. If invoking a LOOKUP formula is justified, that is, Tran_Date in this paricular case in ascending order, there will be just one situation in which #N/A will obtain. That is when IDate < INDEX(Tran_Date,1). Since LOOKUP is very fast, it's a pity to slow down its speed with a scheme that requires "computing twice".

If LOOKUP is indeed justified, the following options would be better:

1)

=IF(IDate<INDEX(Tran_Date,1),"",LOOKUP(IDate,Tran_Date,Jumbo_ND))

2} Add the following at the beginning of Tran_Date:

-9.99999999999999E+307

and at the beginning of Jumbo_ND

=""

and just invoke:

=LOOKUP(IDate,Tran_Date,Jumbo_ND)

unmodified.

The last option is the best.

Entire rows are OK though. Here some thoughts regarding the issue:

http://tinyurl.com/ahysg%5B/url%5D

LOOKUP(9.99999999999999E+307,Reference)

returns the last numerical value from Reference.

Reference can be either a range or an array (a vector). F2:F10 is a range, while {#N/A,#N/A,5,2} is an array.

CHOOSE(Idx,Value1,…,ValueN)

where N is between 1 and 29 inclusive, allows to pick out a value from the list of values Value1,…ValueN whose position matches the Idx value. For example:

CHOOSE(2,MAX(A1:B1),MAX(E1:F1)

picks out the value that MAX(E1:F1) returns.

CHOOSE({1,2,3},VLOOKUP(…),VLOOKUP(…),VLOOKUP(…))

has an Idx that is a constant array of index numbers: {1,2,3}. This causes to pick out all of the values that 3 VLOOKUP will return. The result will look like, e.g.

{205.80,#N/A,#N/A}

or

{217.65,185.43,#N/A}

or

{#N/A,#N/A,#N/A}

LOOKUP with the big number as lookup value will return 205.80, 185.43, and #N/A, respectively.

(Edited by HansV to make URL clickable – see Help 19)

If the retrieval result is a number…

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},VLOOKUP(A6,FoodPrice,4,0),VLOOKUP(A6,Colist,4,0),VLOOKUP(A6,Agriculture,4,0)))

The idiom is due to fairwinds: http://tinyurl.com/6db2f%5B/url%5D

Apparently, either AC7 or Y7 can house #N/A for the formula to evaluate to #N/A.

If you don’t want to clean up AC7 and/or Y7 for such an error, as an alternative, you can set up your formula in a positive mood…

=IF(ISNUMBER(AC7/Y7),AC7/Y7,””)

=SUMPRODUCT(–($W7:$W123=”y”),–($P7:$P123=2),$Y7:$Y123)

would ignore any text values in Y7:Y123 and is slightly faster.

Looks like you want a multi-conditional count…

In H3 enter, copy across to J3 then down:

=SUMPRODUCT(–($B$3:$B$9=$G3),–($E$3:$E$9=H$2))

where B3:B9 house Managers and E3:E9 aid Types, while G3 houses a criterion manager and H2 a type criterion.

Another option is to bulid a pivot table from your data.

What is missing in the attachment is the expected results. Care to provide the desired/expected figures?

Q1. Answer: No. However, the following setup would take care of that.

Assuming that P is numeric and the data starts at row 7…

A1:

=MATCH(9.99999999999999E+307,P7:P65536)

The formula would become, using Sumproduct instead of Sum:

=SUMPRODUCT(–($W$7:INDEX($W$7:$W$65536,$A$1)=A2),–($P$7:INDEX($P$7:$P$65536,$A$1)=B2),–($Q$7:INDEX($Q$7:$Q$65536,$A$1)=C2))

where A2 houses a value like “y” (without double quotes, B2 2, and C2 “L”.

This formula needs just enter instead of control+shift+enter.

Q2. The Sum formula operates on arrays as the SumProduct formula. The star [ i.e., * ] might be thought of as a multple-value AND. It effects vector (matrix) multiplication. The evaluation of a conditional yield a truth-valued vector, that is, something like {TRUE,FALSE,FALSE,TRUE,…}. This set is first coerced into a numerical vector, that is, something like {1,0,0,1,…}. In Excel, 1 and 0 are numerical equivalents of TRUE and FALSE, respectively. After coercion, vectors are multilied and summed. The same thing happens when we use SumProduct. Double negation takes care of coercion. The comma in SumProduct stands for multiplication.

Q3. Answer: 30. The conditionals indeed must be put between parens.

This is recurring issue. Check out, for example:

http://tinyurl.com/6j5kj%5B/url%5D

You could do a multi-key lookup by extending your lookup table (addnum) with an additional column by concatenating its first two columns…

Let B2:E20 on Sheet2 house the lookup table with Item Code in B and Customer in C.

In A2 enter & copy down:

=B2&”,”&C2

Now select A2:E20 and name the selection “addnum”.

This setup allows you to invoke an ordinary VLOOKUP formula…

=VLOOKUP(A2&”,”&B2,addnum,5,0)

where A2 houses an Item Code interest and B2 a customer of interest on, say, Sheet1.

See attachment.

I admit I generate it by counting 9’s while typing.

But one can copy it from the Help file:

Excel specifications and limits > Calculation specifications