Helpdesk Home

Examples with some of these formulas can be downloaded here.

Questions:

1. I  have a list of names and I want to sort them then extract all distinct names from that list, is there a way of doing that using formulas?

2. Is there a way to count the longest streak of negative values using a single formula?

3. SUMIF doesn't seem to work over multiple sheets, how can I sum with a condition using more than one sheet?

5. Are there more formulas that can bypass the "limitations" of Excel when it comes to for instance calculating multiple sheets?

6. VLOOKUP will lookup one value from one column and return a value from another column, but what if there are more than one lookup value in the table?

Answers:

1. First the formula for sorting all names ascending then the second formula for extracting unique names

=IF(ROWS(B$2:B2)<=SUM(N($A$2:$A$11<>"")),INDEX($A$2:$A$11,MATCH(SMALL(COUNTIF($A$2:$A$11,"<="&$A$2:$A$11),
SUM(N($A$2:$A$11=""))+ROWS($A$2:A2)),COUNTIF($A$2:$A$11,"<="&$A$2:$A$11),0)),"")

=IF(ISNA(MATCH(0,COUNTIF($C$1:C1,$B$2:$B$11),0)),"",INDEX($B$2:$B$11,MATCH(0,COUNTIF($C$1:C1,$B$2:$B$11),0)))

both need to be entered with ctrl + shift & enter, if done correctly it will wrap the formulas in curly brackets

2. Yes, it's easier to use a help column that tests each value individually with a Boolean value (TRUE or FALSE) then count that value using COUNTIF but it can be done using a complicated array formula:

=MAX(MMULT(TRANSPOSE(--($A$4:$A$22<0)),(TRANSPOSE(ROW(INDIRECT("1:"&COUNT(--($A$4:$A$22<0)))))
>=ROW(INDIRECT("1:"&COUNT(--($A$4:$A$22<0)))))-SIGN(MMULT(--(ROW(INDIRECT("1:"&COUNT(--($A$4:$A$22<0))))
<=(TRANSPOSE(--($A$4:$A$22<0)=0))*TRANSPOSE(ROW(INDIRECT("1:"&COUNT(--($A$4:$A$22<0)))))),--(TRANSPOSE(
ROW(INDIRECT("1:"&COUNT(--($A$4:$A$22<0)))))>=ROW(INDIRECT("1:"&COUNT(--($A$4:$A$22<0)))))))))

enter it with ctrl + shift & enter, as you can see the example has 3 consecutive negative values (high-lighted in yellow, obviously it can be adapted for positive values as well).

3. There is a workaround using INDIRECT and SUMPRODUCT that will let you use SUMIF over multiple sheets:

=SUMPRODUCT(SUMIF(INDIRECT("'Quarter"&ROW(INDIRECT("1:4"))&"'!A2:A21"),A2,INDIRECT("'Quarter"&
ROW(INDIRECT("1:4"))&"'!B2:B21")))

will sum sheets Qarter1:Quarter4 range B2:B21 where A2:A21 equals A2 from the summary sheet

4. Yes, a workaround can look like this:

=SUMPRODUCT((ROW(INDIRECT("B1:B10"))<=MATCH(A1,'[Closed Workbook.xls]Sheet A'!$A$1:$A$10,0))*IF(ISNUMBER
('[Closed Workbook.xls]Sheet A'!$B$1:$B$10),'[Closed Workbook.xls]Sheet A'!$B$1:$B$10))

If "Closed Workbook" is closed the above formula will still work whereas the original formula will return a #VALUE! error.

5.Here are 2 more:

=INDEX(FREQUENCY(First:Last!$B$1:$B$100,A1*{0.99999999999999;1}),2)

With a  numeric value in A1 in the summary sheet it will count the occurrences of that value in B1:B100 in all sheets
between First and Last

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&MySheets&"'!A1:A50"),A1)>0,0))&"'!A:B"),2,0)

Where a list of sheet names is named "MySheets" and it will allow a de facto VLOOKUP over multiple sheets.
The latter formula needs to be entered with ctrl + shift & enter.

Top of the page

6. You can use a combination of functions to return more than one value, with the lookup value in A1 and the lookup table
 in First!$A$1:$B$20 where we want to return the values in B1:B20

=IF(ROWS(B$1:B1)<=COUNTIF(First!$A$1:$A$20,$A$1),INDEX(First!$B$1:$B$20,SMALL(IF(First!$A$1:$A$20=$A$1,
ROW(First!$A$1:$A$20)-ROW(First!$A$1)+1),ROWS(B$1:B1))),"")

Entered with ctrl + shift & enter and copied down, note that it would be better to use multiple formulas or even better to use
filters.