7,010 reputation
1712
bio website pending
location England
age 50
visits member for 3 years, 3 months
seen 18 hours ago

I'm keen on excel.....no, really.....I contribute to some other forums on the web, mostly excel formula related - short, long, complex......or very, very complex! I'm your man. I'm particularly interested in date related formulas.


2d
comment Excel: Sum a column of text excluding duplicates in another column of numbers
No problem - Can you "accept" my answer please
Mar
27
comment Excel: Sum a column of text excluding duplicates in another column of numbers
I edited my answer to fit your sheet - google sheets isn't the same as Excel - some formulas need to be different - you don't have "custom" on it's own in column G - you need a different approach to search for it within other text
Mar
27
revised Excel: Sum a column of text excluding duplicates in another column of numbers
added 456 characters in body
Mar
26
revised Excel: Sum a column of text excluding duplicates in another column of numbers
added 4 characters in body
Mar
26
comment Excel: Sum a column of text excluding duplicates in another column of numbers
This works for me - as long as you don't have any rows that contain "custom" in G but a blank in N - in that case you'd get #DIV/0! error - did you use CTRL+SHIFT+ENTER - but you could try my suggestion too - it should be more efficient if you have numbers in col N
Mar
26
answered Excel: Sum a column of text excluding duplicates in another column of numbers
Mar
23
comment excel formula to return value based on multiple criteria
Do you expect to get an exact match for the lookup date - for example would you want to look up 2015.03.11? (and presumably get the values for the previous date in the table)
Mar
23
comment Is there a union operator in Excel?
Now that SUM, AVERAGE etc. functions allow up to 255 arguments I doubt this is as helpful as it might have been once.....but another use of the union is to allow more discontiguous cells to be used e.g. =SUM(A1,B1,F1) is using 3 references while =SUM((A1,B1,F1)) is only 1
Mar
23
comment Is there a union operator in Excel?
I came across this just now while trying to answer another question - the above is correct but I think, technically, a union needs to be enclosed in parentheses - if you are using SUM function then using =SUM(A1,B1,F1) is just using the functionality of SUM which allows you to add multiple references - you can see a union properly in SMALL function, e.g. SMALL((A1,B1,F1),2) - if you don't use the internal parentheses that doesn't work - You can also use this in RANK and FREQUENCY functions
Mar
15
comment Your formula is missing a parenthesis --)
Which version of Excel are you using? In Excel 2007 and later versions it's easier to use IFERROR function because you can avoid repeating the VLOOKUPs. Note: none of your VLOOKUPs will work as written because you are trying to retrieve a value from the 4th column of a 2 column array. If you are matching with column C and returning a value from D the 4 should be a 2
Mar
12
awarded  Tenacious
Mar
12
comment SUM IF FREQUENCY MATCH
There should be no maximum value except that implied by the formula - if you have a 878 row range then obviously there can't be more than 878 different values in one column - other than that there's no restriction
Mar
12
comment SUM IF FREQUENCY MATCH
That formula looks OK, what's the problem?
Mar
10
revised Using a Defined Name in a Formula
added 221 characters in body
Mar
10
revised Using a Defined Name in a Formula
added 221 characters in body
Mar
10
comment Using a Defined Name in a Formula
Off topic a little.....but assuming named2 is a contiguous vector it can be either vertical or horizontal and the same function works for either - INDEX(named2,RANDBETWEEN(1,5)) - When the range is a vector (single column or row) you only need one argument to define the position
Mar
10
answered Using a Defined Name in a Formula
Mar
10
answered Create Excel Array In Formula Text
Mar
9
answered Why doesn't this array formula work when cell reference contains a formual?
Mar
9
comment Why doesn't this array formula work when cell reference contains a formual?
The CONCATENATE function is producing a text string that probably isn't recognised as a date - what's in B2 and A4 exactly?