Search results

I was reading another thread and it reminded me that AGGREGATE() is also an option to do this if you are using Excel 2010 or later: =AGGREGATE(9,6,A2:A10) The 9 stands for SUM. The 6 stands for ignore error values.

You can also use SUMIF which avoids the need to use an array formula, eg =SUM(SUMIF(A2:A10,{"<0",">0"}))

Just in case the question is about the AGGREGATE() worksheet function; MSDN information can be found here: http://office.microsoft.com/en-gb/excel-help/aggregate-function-HA010338704.aspx It is only available in Excel 2010 or later.

Yes, for example you could write a VBA UDF to get a distinct count.

Ah okay - I was using your data from post 1. If it goes in E1 then you'll need to adjust the ranges to avoid circular references: =SUM(--(FREQUENCY(IF(A1:D1<>"",MATCH(A1:D1,A1:D1,0)),COLUMN(A1:D1)-COLUMN(A1)+1)>0))

It works fine for me. Can you load a sample workbook with the formulas in for us to look at?

Using your sample from the 1st post, put this formula in F1 and fill down: =SUM(--(FREQUENCY(IF(A1:E1<>"",MATCH(A1:E1,A1:E1,0)),COLUMN(A1:E1)-COLUMN(A1)+1)>0)) This is an array formula so when you type it into the formula bar you must complete the entry with CTRL+SHIFT+ENTER...

Yet another... =MAX(IF(TEXT(A1:A10,"myy")=TEXT(E1,"myy"),B1:B10)) This is an array formula so must be entered with CTRL+SHIFT+ENTER.

Very little information to go with... here is a general example: Sub example() Dim varResult As Variant Dim varLookUp_Value As Variant Dim rngTable_Array As Range Dim lngCol_Index_Num As Long Dim blnRange_LookUp As Boolean varLookUp_Value =...

Welcome back and I'm glad to hear that you are well on the way to recovery.

For SQL Server you might want to check out http://www.sqlservercentral.com/ The site has blogs, articles, question forums etc. When you join you will get daily emails with SQL tips (which you can turn off if you don't want them).

Hi Sajan, Just FYI, because you're using an array constant you can just use SUM (no need for CSE). =SUM(SUMIF(A2:A11,{"<300";">500"})) Hi Dave, The SUMPRODUCT() formula you posted will return the wrong result because it requires the cells in column A to be both less...

Here's another way, although it'll handle empty cells in column B differently (it will ignore them): =SUMPRODUCT(COUNTIFS(B2:B4614,"<="&$N$3,$A$2:$A$4614,J2:J26))

Welcome to the forum. One formula you could use in G48 would be: =LOOKUP(F48,{0,5,10},{35,30,25}) If the pricing structure gets more complicated or larger then it'd be a good idea to use a lookup table rather than using array constants in the formula. That'll make your worksheet easier to...

The accepted answers on Q5 are still wrong. I don't want to post the reasons on here in case it ruins the quiz for people who haven't taken it yet - I'd be happy to explain my reasoning by email if an Admin/Mod pings me at colinleggblog at gmail dot com.

I think this is because your colleague has the Windows hide file extensions setting turned off. Have a read of this article: http://www.cpearson.com/excel/FileExtensions.aspx where Chip pearson says:

If you're using (or require compatability with) pre Excel 2007 (so SUMIFS() is unavailable) then you can also do it with SUMIF(): =SUMIF(A1:A20,">-5")-SUMIF(A1:A20,">=5")

Hi, Take the sum of all of the values (eg SUM(A:A)) and then subtract the SUMIF(s) values of apples, oranges etc... which you've already calculated.

Hi Asseem, See if this gives you the results you want: =MEDIAN(A2*{1.1,0.9},B2)=B2 Where 100 is in A2 and 118 is in B2.

Hi, When you say you're learning SQL, do you mean T-SQL? Or SQL databases? Or both? Something I've been learning for the last couple of years is MS SQL Server database development (NB. developer, not DBA) and I can thoroughly recommend Rob Vieira's "Beginning Microsoft SQL Server 20[yy]...

Provided the closed workbook is not password protected, you could write to it using ADO. This however, is not a simple task - particularly because using ADO with Excel files has many 'features' and bugs - and you will only be able to insert or update datarows (not delete them) so, if you need a...

Hi, Do you mean that the userform doesn't render properly? If that's the case use UserForm1.Repaint, otherwise you'll need to describe in more detail the problem.

Hi, You can show the userform modelessly which will allow other code to run while the userform is showing. You can either do this explicitly in your code when you show the userform, eg: UserForm1.Show vbModeless Or you can do it at design time by setting the userform's ShowModal property to...