Replies

If the single result (or last value) you need is either a text value or a numeric value, here are two options:

[1] A multi-cell set up:

AA24:

=INDEX(AA7:A23,IF(COUNT(AA25:AA26)=2,MAX(AA25:AA26),SUMIF(AA25:AA26,”#N/A”)))

AA25:

=MATCH(9.99999999999999E+307,AA7:AA23)

AA26:

=MATCH(REPT(“z”,255),AA7:AA23)

[2] A single-formula approach:

AA24:

=LOOKUP(2,1/(ISNUMBER(AA7:AA23)+ISTEXT(AA7:AA23)),AA7:AA23)

X2:

=EOMONTH(MAX(B2:B52),-1)+1

Y2:

=EOMONTH(X2,0)

Then invoke:

=SUMIF($B$2:$B$52,”>=”&X2,$G$2:$G$52)-SUMIF($B$2:$B$52,”>”&Y2,$G$2:$G$52)

The foregoing is the same suggestion as the one maxfilia10 forwards. The difference is that this is meant to fit to your data and the calculation you want..

Given your formula, #N/A can only occur when either W2 or F2 is smaller/less than Calendar!$A$1.

If Calendar!$A$1:$C$3489 is sorted in ascending order on its first column, a state of affairs that justifies your setup for the VLOOKUP bits (No 4th arg, meaning 1 or TRUE), you don’t need ISERROR()…

Care to indicate what kind of stuff you have in Calendar!$A$1:$A$3489?

That’s a formula for “Sum exactly/strictly N largest”, as Hans also points out. A typical situation for which such a calculation makes sense is:
Total of N best scored quizzes in order to calculate an average.

If you want to include the ties of the Nth largest value, see Hans’s reply with SumIf.

Since you are on Excel 2003, convert the area you autofilter to a list by means of Data|List|Create List. The list functionality includes AutoFilter along with significant options.

What follows is a faster way of summing stricly 6 largest …

=SUMIF(B2:K2,”>”&B4)+(6-COUNTIF(B2:K2,”>”&B4))*B4

where B4 houses:

=LARGE(B2:K2,6)

than invoking the volatile INDIRECT in an array formula.

Braddy,

There is no need for ISNA for these formulas

=IF(ROW()-ROW($E$5)+1<=$E$3,LOOKUP(ROW()-ROW($E$5)+1,$D$5:$D$19,$C$5:$C$19),"")

=IF(E5″”,COUNTIF($C$5:$C$19,E5),””)

will never return #N/A. unless the range to process is empty.

To cover the last possibility, I’d suggest to change the formula in E3:

=LOOKUP(9.99999999999999E+307,$D$5:$D$19)

to:

=LOOKUP(9.99999999999999E+307,$D$3:$D$19)

Using a fast formula system…

C4:C19 houses the data of interest, including the label/header.

D3: 0

which is mandatory.

D4: Idx

which is just a label.

D5, copied down:

=IF((C5″”)*ISNA(MATCH(C5,$C$4:C4,0)),LOOKUP(9.99999999999999E+307,$D$3:D4)+1,””)

E3:

=LOOKUP(9.99999999999999E+307,$D$5:$D$19)

E4: Distinct Products

which is just a label.

E5, copied down:

=IF(ROW()-ROW($E$5)+1<=$E$3,LOOKUP(ROW()-ROW($E$5)+1,$D$5:$D$19,$C$5:$C$19),"")

F4: Count

which is just a label.

F5, copied down:

=IF(E5″”,COUNTIF($C$5:$C$19,E5),””)

The problem you want to solve belongs to the class of Top N problems, where N = 1, 2,…

What follows is a formula system I devised to tackle with such problems. The system results agree with the results one would obtain with a pivot table.

The data in C4:AA4 consists of multiple items (considerations like Service, Knowledge, Experience) per cell obtained by means of surveys. (See the attachment for references.)

Note that the data is a bit altered to better illustrate the point of the formula system.

The problem: Create a Top 3 list of considerations, based on their occurrence frequencies.

C5:C11 lists the ‘conisderations’ of interest.

D5, copied down:

=COUNTIF($C$4:$AA$4,”*”&C5&”*”)

calculates the frequency of occurrence per consideration.

E5, copied down:

=RANK(D5,$D$5:$D$11)+COUNTIF($D$5:D5,D5)-1

assigns a ranking per consideration.

D13: 3

indicates N of Top N you want.

D14:

=MAX(IF(INDEX(D5:D11,MATCH(D13,E5:E11,0))=D5:D11,E5:E11))-D13

which must be confirmed with control+shift+enter, not just with enter.

This “kernel” formula calculates the ties of the Nth value (frequency of occurrence).

D16, copied down:

=IF(ROW()-ROW($D$16)+1<=$D$13+$D$14,INDEX($C$5:$C$11,MATCH(ROW()-ROW($D$16)+1,$E$5:$E$11,0)),"")

creates the correct Top 3 list of considerations you are after.

No need to call UPPER three times for IF is not case-sensitive.

C3:

=IF(LOOKUP(REPT(“z”,255),$C$1:C2)=”T”,IF(B3=”F”,”F”,0),IF(A3=”T”,”T”,0))

custom formatted as: [=0]””;General and copied down might be faster.

Adding to Hans’s observation, see my contrib in:

http://tinyurl.com/83b2x%5B/url%5D

Since you are on Excel 2003, you could turn the data area into a list by running Data|List|Create List. The formulas that refer the ranges from a such list will automatically update whenever you add new records to the data area. The setup makes named ranges unnecessary.

If the formula needs to be in the cell immediately after the last cell of the range of interest (which is A1:A100 in your example)…

In A101 enter:

=AVERAGE(OFFSET(INDEX(A:A,ROW()),-1,0,-N,1))

where N is a value like 12, meaning “last 12”.

If you’d want to enter the formula in a cell outside column A….

=AVERAGE(OFFSET($A$1,MATCH(9.99999999999999E+307,$A$1:$A$100)-N,0,N))

or with a bit control:

=IF(COUNT($A$1:$A$100)<=N,AVERAGE($A$1:$A$100),AVERAGE(OFFSET($A$1,MATCH(9.99999999999999E+307,$A$1:$A$100)-N,0,N)))

where N is the same as above.

BTW, if you want to use an array constant to represent the criteria set…

=SUMPRODUCT((D2:D22={“Art”,”Admin”})*(F2:F22))

can be re-written as a SumIf formula:

=SUM(SUMIF(D2:D22,{“Art”,”Admin”},F2:F22))