Replies

Given your intention, wouldn’t

=LOOKUP(MIN(A2+$F$1,100),{0;70;77;80;87;90},{“F”;”C”;”C+”;”B”;”B+”;”A”})

where F1 a small amount for encouragement like 1 and A2 a score, suffice?

Since you are on Excel 2003, select the data area (including the headers) and run Data|List|Create List. All formulas referring to (parts of) the list will adjust automatically to changes to it.

A tad bit faster multiconditional counting can be effected with:

=SUMPRODUCT(–($A$2:$A$22=1),1-($B$2:$B$22=0))

Hoi Hans,

That’s right.

Closer to the SumProduct formula in behavior:

=COUNTA(Range)-COUNTIF(Range,””)

The error values would also be counted in by the foregoing formula.

However, I just assumeed that only text and numeric values would be of interest.

Aladin

=COUNT(Range)+COUNTIF(Range,”?*”)

=IF(N(X25),(SUM(E25:H25)*200000)/X25,””)

Consider exploiting the Data|List|Create List option that your Excel 2003 comes with.

Since the OP is on Excel 2003, he can convert B1:D37 into a list by means of Data|List|Create List to obtain automatic formula copying and range adjustment in formulas that reference the list area.

BTW, although not a serious issue here, it would be better to invoke:

=INDEX($B$2:$B$37,MATCH(F1,$D$2:$D$37,1)+(LOOKUP(F1,$D$2:$D$37)F1))

where F1 houses the condition number.

Given:

2,3
2,4
4,2
2,3
2,blank

would the result be 4?

For excluding blanks, the idiom would be:

=SUM(IF(A1:A200″”,1/COUNTIF(A1:A200,A1:A200)))

followed by control+shift+enter.

=COUNTIF(Range,”*(RM)*”)

> The names in brackets could be names or ranges as desired for looking up. They could be dynamic ranges that grow as more items are added…

Since he is on Excel 2003, he can better convert the lookup tables into lists with Data|List|Create List, which makes defining ranges by means of dynamic formulas superfluous.

Version 3.9 (the latest at this moment) of the morefunc.xll add-in would allow:

=COUNTDIFF(A1:A100,,””)

which behaves like the SumProduct version vis-a-vis empty cells and formula-blanks.

Just in case: Your example

=SUMPRODUCT(Qty*(Ship_From=A7)) or {=SUM((Qty*(Ship_From=A7)))}

is not-multi-conditional. A SumIf formula is then more appropriate for it is faster:

=SUMIF(Ship_From,A7,Qty)

If you use ISNUMBER instead of ISERROR, you’ll have a more natural evaluation:

=IF(ISNUMBER(VALUE(B7)),B7+0,”Not Number”)

or

=IF(ISNUMBER(B7+0),B7+0,”Not Number”)

The last VLOOKUP (in the original) seems to suggest that ‘Raw materials’!$A$2:$CQ$1799 is sorted in ascending order on its first column…

What follows might be more efficient while compact…

[1] In case the lookup area on Raw materials is sorted:

=IF(LOOKUP(REPT(“z”,255),CHOOSE({1,2},””,VLOOKUP(‘Enter formula’!$H7,’Raw materials’!$A$2:$CQ$1799,95)))=”colorant”,VLOOKUP(‘Enter formula’!$H7,’Raw materials’!$A$2:$CQ$1799,79),”-“)

[2] For unsorted data:

=IF(LOOKUP(REPT(“z”,255),CHOOSE({1,2},””,VLOOKUP(‘Enter formula’!$H7,’Raw materials’!$A$2:$CQ$1799,95,0)))=”colorant”,VLOOKUP(‘Enter formula’!$H7,’Raw materials’!$A$2:$CQ$1799,79,0),”-“)