Search:

Insert two new worksheets and name them First and Last. Insert ALL relevant sheets between the two and use,

=SUM(First!:Last!B29)

=SUMPRODUCT(($A$2:$A$27-DAY($A$2:$A$27)+1=$F2)+0,($B$2:$B$27=RIGHT(F$1,1)+0)

Where F2 houses the first day of the month

Try,

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

or with the Morefunc addin

=COUNTDIFF(A1:A100)

This one I learned from Aladin and needs the Morefunc addin.

Caveat, it doesn't take into account the grouping of 24 as I came up with 2531 and 2535

In B1, enter =MIN(A1:A360)
In B2, enter...

(Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

See,

http://www.decisionmodels.com/optspeedk.htm

Try array entered,

=AVERAGE(IF(B3:B96="george",A3:A96))

=AVERAGE(IF((B3:B96="george")*ISNUMBER(A3:A96),A3:A96))

I don't see 0 values only blanks

John,

Could the N function be used in place of ISNUMBER?

An alternative to OFFSET and COUNTA,

=$A$1:INDEX($A$1:$A$65536,MATCH(9.99999999999999E+307,$A$1:$A$65536))

for numeric data

=$A$1:INDEX($A$1:$A$65536,MATCH(REPT("z",255),$A$1:$A$65536))
...

Try,

=SUMIF(A1:A100,"<>#N/A")

or rid the cells of the #N/A and just use SUM.

=VLOOKUP(B7,CHOOSE(MATCH(D5,{"T1","T2","T3","T4","T5","T6","T7","T8"},0),T1,T2,T3,T4,T5,T6.T7.T8),2,0))

Where T1, T2 etc are table names.

Some options,

=SUMPRODUCT((A1:A30<>"")/COUNTIF(A1:A30,A1:A30&""))

With the Morefunc addin

=COUNTDIFF(A1:A30)

For numeric only

Thank-you to both you and Steve!

Is this syntax correct?

=IF(ISERROR(vlokup(A1,table,)),"",VLOOKUP(A1,table,))

Steve,

See if Excel accepts this formula, without a named range named table and vlokup

=IF(ISERROR(vlokup(A1,table,2,0)),"",VLOOKUP(A1,table,2,0))

As an aside, Vlookup will only return a #N/A error for no exact match and #REF for the column to return which is not in the range. Use ISNA instead of ISERROR if you must. ISERROR will also mask...

Why not clean up your data in the lookup column by doing one of the following,

Run ASAP Utilities at

www.asap-utilities.com

Run this macro,

Sub TrimALL()
'David McRitchie 2000-07-03 mod...

couple other options,

A2:

1-June-05 (or 6/1/2005)

B2:

=EOMONTH(A1,0)

I don't know if one is more efficient than the other. OFFSET is a volatile function, whereas Index/Match is not. What you can do to eliminate a couple of function calls is to enter the column/row...

=INDEX(B2:J9,MATCH(--LEFT(N2,1),A2:A9,0),MATCH(--RIGHT(N2,1),B1:J1,0))

Using the addin from Morefunc,

=MCONCAT(C3:CP3," ")

List your sheet names ina range and name that range. In this example, I've named it "sheets" without the quotes. Then use,

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!B3:B100"),2))

Another...

Ahhhh....minutes and seconds...I apologize for not reading more carefully!

Another option,

=TEXT(A1,"00:00")+0

And format as time.

Are these elapsed times? Is there any time you would want 7:49 PM or 19:49?

In addition to Han's solution, you can also "coerce" text time/date.

=c2>"3:00"+0

Depending whether the columns houses text or numeric,

=$B$6:INDEX($B:$B,MATCH(REPT("z",255),$B:$/forums/images/smilies/cool.gif) for text
...