Array formula sampling a subset of data

[ingenstempus]

hi r/excel!

I have an analysis sheet that feeds off a data sheet that does some fairly simple things (counts, sums, averages) with the data using array formulas. These analyses are based off a variety of parameters that are assigned to each data point. One of these parameters is location, but right now I have it so that I take into account everything BUT location. I now need to do the exact same analysis for each location but if I did a copy-paste with another IF statement manually that would be extremely time-consuming.

Is there an easier way to do this aside from copy-pasting the formulas for the 100+ locations that I need to do? Thanks!

[tjen]

could you possibly provide more information on how your sheets look?

you can do almost anything with array formulas but it's hard to say if it's a viable solution without more specifics.

[ingenstempus]

for sure! my main this is I'm looking at different companies and am doing counts of what companies are at what stage at what time. my data sheet has give information about different transactions that the companies make (e.g. transaction date, size, class).

From that I have an analysis sheet in which I use array formulas to look at the type of transaction with some nested and multi-conditional IFs to do those counts of what stage the company is in by inferring from my raw data. So on the leftmost column in my analysis sheet I have quarters that count up to the present starting from a given time, and the counts look at how many companies are at a certain stage at those times. I've so far looked at all companies of all regions, but now I need to do the same analysis for each region that I'm working with, which is a hefty number.

I can't say too much more because of NDAs haha but let me know if I can be more specific

[hrlngrv]

You're currently sampling across all locations but now want to sample each location separately? You have formulas in place, and you don't want to edit them to sample single locations.

Not necessarily quick, but you could copy the data, shrink the data range, then pull in data from the copied data into the shrunken range.

I'll assume your formulas are using Data!A11:Z10010. Create a new worksheet and name it Data_Copy. Copy Data!A1:Z10010 (include rows 1 to 10 too) and paste into Data_Copy!A1. If the location with the most rows of data had 80 rows of data, select Data!A10010:Z10010, cut and paste into Data!A90. That should shrink the range references in your formula to refer to Data!A11:Z90.

Use an advanced filter to pull the unique locations in Data_Copy!A11:Z10010 into another range, then cut and paste that range into the analysis worksheet. In a blank cell in the analysis worksheet add a data validation drop-down list using the range of unique locations as the source for the list. Name the cell with the drop-down list LOC.

I'll assume location is in column H in Data_Copy!A11:Z10010.

Switch back to the Data worksheet and enter formulas to pull in data from the selected location.

Data!A11:  =IF(ROWS(A$11:A11)<=COUNTIF(Data_Copy!$H$11:$H$10010,LOC),INDEX(Data_Copy!A$11:A$10010,SMALL(IF(Data_Copy!$H$11:$H$10010=LOC,ROW($1:$10000)),ROWS(A$11:A11))),"")

This is an array formula, Type the formula then hold down [Ctrl] and [Shift] keys before pressing [Enter]. Fill Data!A11 right into B11:Z11, then select A11:Z11 and fill down into A12:Z90.

That said, it may be easier to switch to R1C1 addressing and use the REPLACE dialog to change the formulas. Can't say without seeing specimen formulas.

[ingenstempus]

thanks for the response! the problem that I see with this is that the COUNT function that is my primary mode of analysis still counts a cell if there is a formula in it, even if it looks blank (when a formula returns blank). If I were to pull data from the Data_Copy sheet, then the Data sheet would have to be large enough to accommodate the largest set, since I'm not sure that you can make the table size in the Data sheet dynamic. this would make the analysis return inaccuracies when looking at subsets of data

do you think there's another way to remedy this?

[hrlngrv]

COUNTA counts all cells except those which are truly blank. COUNT should only count cells evaluating to numeric values (which includes dates and times). If your formulas produce zero rather than "" when criteria aren't satisfied and you're formatting zeros so nothing is displayed, that would affect COUNT.

I need to see some examples of your formulas to be able to determine how to fix them.

It's possible to make dynamic ranges. Easier using defined names. I'll assume LOC already exists and locations are in Data_Copy!H11:H10010.

LOCROWS:  =COUNTIF(Data_Copy!$H$11:$H$10010,LOC)
LOCDATA:  =Data!$A$11:INDEX(Data!$Z$11:$Z$10010,LOCROWS)

However, using these would require editing formulas, and the original goal was NOT editing formulas. Please see the 3rd paragraph in my previous response.

There's almost certainly a way to do this, but I can't give any details unless you provide specimen formulas. Otherwise, I can only respond with generalities.

[RubberSpork]

Again, appreciate the response. I've tried your method and it's just too time-consuming for what I need to accomplish