All Questions
92
questions
6
votes
2
answers
934
views
Use functions like Countif, Countblank etc. to refer to discontiguous ranges.
Is there a way to use array formulas or some other feature to essentially have an excel function that means.
=Countblank({A1,A4:A6,A112:127,B29:B38}) etc.
Essentially, I want to refer to multiple ...
- 774
3
votes
3
answers
24k
views
MS Excel: Sumproduct only visible rows (use sumproduct with subtotal(9,array))
Pretty straightforward.
I want to use a SUMPRODUCT but only for visible rows.
I've tried =SUMPRODUCT(A2:A10, SUBTOTAL(9,B2:B10)) ... didn't work, even as an array formula.
I'm probably missing ...
- 155
3
votes
1
answer
2k
views
Make an array formula in excel calculate only on active rows
When excel calculates formulas over a column (e.g. a lookup), it's smart enough to only calculate until the last active row, rather than also running on all the blank rows.
With array formulas, it ...
- 774
3
votes
2
answers
3k
views
Excel, how to use text functions on lookup array without putting in another column?
If I'm using a vlookup, or a match function but I need to do some mumbo jumbo on my lookup material, is there a way of doing this in the lookup array without simply adding another column?
Let's say I ...
- 774
2
votes
2
answers
3k
views
In Excel, can I "splice" multiple Dynamic/Spilled Arrays into a single Dynamic/Spilled Array?
IN A NUTSHELL
I have four (say) columns of data, each a Dynamic/Spilled Array. I'd like to "stack" them into another column so as to produce a single Dynamic/Spilled Array comprising the "splice" of ...
- 464
2
votes
2
answers
244
views
Using GCD Function With Arrays in Excel
I'm having trouble determining GCD from a dataset. I'm trying to determine the quantity of items in a box of any given item in a warehouse without physically looking at each item on the shelves.
...
- 25
2
votes
1
answer
2k
views
Excel Formula To Show Top X Percentage Of An Array
Using the simple data set below, starting in A1, with headers:
Order Time
a 1
b 1
c 2
d 4
e 4
f 5
g 6
h 7
i 500
j 600
I want to know in ...
- 7,485
2
votes
2
answers
1k
views
How to flag rows with the pattern non-blank, blank, non-blank using Excel
I would like your help with something I have been trying to figure out for the past two days.
I have data with 1000's of rows and several columns. I want to insert a column with a (array) formula ...
- 257
2
votes
1
answer
455
views
Trying to create a formula to calculate a rate value per row in Excel
I think this is to do with a formula over an array but I can’t get it to work.
I’m looking for the easiest method to do the following.
Say I have this setup, columns are 'jobs' and rows are '...
- 23
2
votes
1
answer
3k
views
Find position of first uppercase character in excel string
I need to find the position of the first uppercase character in an Excel string. There are lots of resources on the web that have the same (or effectively the same) solution for this:
=MIN(IF(ISERROR(...
- 191
2
votes
1
answer
622
views
How to count number of cells over multiple worksheets whose values are in between a given range?
I have an Excel workbook with numerous worksheets containing data like:
'DATA_1' 'DATA_2' 'DATA_3'
A B A ...
- 638
1
vote
2
answers
31k
views
In Excel I need to find data matches between two arrays - one horizontal and one vertical
In Excel 2010 (or 2007 - I have both, although my OS is only Win7 32 bit as a limitation for some legacy applications we run), I need to find how I can find and return the matching value from two data ...
- 74
1
vote
2
answers
202
views
Count max values compared with previous array
I try to count the number of values in a row that are equal are greater to MAX() value of a (part) of the column above.
For example: compared to the 3 cells above itself. In E comes the 'count'.
i ...
- 21
1
vote
2
answers
298
views
LookUP Value returning multiple concatenated values
I have two columns, one is a 'Category' with a simple code and the other column named 'Tree/group qualities' with one, or many of the following categories that are, if multiple, concatenated using ...
G.Spanish
1
vote
2
answers
3k
views
Excel - array function to remove spaces
I have a column of text items that can contain spaces (empty cells) in between items. On another sheet, I'm trying to use an array formula over the existing range to filter out those empty spaces.
...
- 13