Search results

@Haseeb A Hey, that's cool. I'd missed that the formula can be done with just SUM() rather than SUMPRODUCT(). [My formula variation: =SUM(--(FREQUENCY(A1:A4,A1:A4)>0)) ] Now I'll have to go and update that blog post... :rolleyes:

In terms of native functions you can use a formula such as this: =SUMPRODUCT(--(FREQUENCY(A1:A4,A1:A4)>0)) For more information on this formula see here. But I agree with Deepak that a custom UDF could be more performant. I had a go at writing a COUNTDISTINCT() function which uses a...

@Somendra Misra Okay, cool.

You must have a special version of Excel 2010! :) I don't have Excel 2010/3 available to hand to double check this, but I think it was introduced only in 2013. It was one of the new 2013 worksheet functions I listed here.

If you have Excel 2013+ and want to specifically target #N/A errors then you can use the IFNA() function. =IFNA(VLOOKUP(Lookup_Value,Table_array,Col_index_num,Range_lookup),"") If you have Excel 2007+ and want to target all errors you can use IFERROR()...

@Somendra Misra If B2 contains "aaaaa3aaaaaaaaaa3aa" so the 17th character is a "3" then what will your formula do?

In that case I think your best bet would be to add a helper column with a formula which extracts the 17th character. A formula like this copied down the column: =MID(B2,17,1) You can also use additional helper columns to extract any other data of interest. Once you've done that you can use a...

If you want the 17th character to only be a "V" then the formula is simpler: =SUMPRODUCT(--A2:A5,--(MID(B2:B5,17,1)="V"),--(C2:C5="XX"),D2:D5) or =SUMIFS(D2:D5,A2:A5,TRUE,B2:B5,REPT("?",16)&"V??",C2:C5,"XX") Neither are case sensitive. The SUMIFS() formula is faster and also validates that...

Even better, if you have XL 2007 or later you can do this with SUMIFS(). =SUM(SUMIFS(D2:D5,A2:A5,TRUE,B2:B5,REPT("?",16)&{3,4}&"??",C2:C5,"XX"))

Here's another variation which uses MID() to get the 17th character: =SUMPRODUCT(--A2:A5,--ISNUMBER(MATCH(MID(B2:B5,17,1),{3,4}&"",0)),--(C2:C5="XX"),D2:D5)

Hi, What driver are you using? I doubt that Cast() is a valid function and that Unsigned Integer is a valid data type. Also I'm not sure what the ampersand in the string after the cast is meant to do? Perhaps post your full code / link to sample file?

It's a fairly common bug which affects ActiveX controls. It's particularly prevalent when you remote onto another computer which has a different screen resolution. Incidentally there's an even worse bug with ActiveX listboxes which can cause Excel to crash if the zooms are different on different...

It's truly amazing: each time I log in here you seem to have reached the next milestone! Good work, old boy! :)

If you need that many conditions in your OR() then you really should look at alternative ways to solve your requirements. It's hard to be specific about that without more information.

Congratulations, @Faseeh!

@ThrottleWorks There was some discussion here, although my reply in post #21 has been mangled by some erroneous quote/code tags - perhaps a moderator would fix it? EDITED (SirJB7) Just read it, followed the link, gone to #21 and everything seemed Ok, surely yet fixed. If it's something I'm as...

This could end up being a very long thread! ;) To add an Excel VBA perspective, MZ-Tools and CodeCleaner are my absolute essentials.

@iferror Just to add, to replicate your formula you could use the built-in TRIMMEAN() function. ie. =TRIMMEAN(A1:A10,2/COUNT(A1:A10))

Congratulations and well done, Narayan! Looking forward to reading your next 1000... :)

Congratulations to both of you. It's a fine recognition for all of your hard work on here. Keep it up!

Congrats, Hui, I take my hat off to you! :)

Congratulations on making so many valuable contributions to the community! :cool:

To get the numeric count you could use: =COUNT(B5:B24)

Change the SUM() to SUMPRODUCT() since the named range needs to be dereferenced into an array. eg. =SUMPRODUCT(SUMIFS(tbl1[amt],tbl1[trans],lst1)) *untested Also note that you could use SUMIF instead of SUMIFS in this particular formula since there is only 1 criteria which is an array.

Happy New Year, everyone. I hope 2014 brings you happiness.