Alternative to Array Formula for summarizing/counting table information

[cgrynberg]

Hello,

I am trying to summarize information contained in a "calendar". The information looks like this:

	27/Mar/16	28/Mar/16	29/Mar/16	30/Mar/16	31/Mar/16	01/Apr/16
John	1	1	1	1
Oliver			2	2	2	2
Mac	F	F	F	F	F	F

I basically need for each name a count of each of the variables. I have numbers and letters. And the user can select start and end dates. the summary looks like this:

Select Start Date: Mar/17
Select End Date: Mar/17

	1	2	F
John	4
Oliver		3
Mac			5

The calendar has hundreds of lines (people) and there are 4 years of planning (therefore over 1,000 columns) plus other linked sheets. I can do this using array formula but obviously my spreadsheet which already has 7MB crashes with even a small quantity of those. Is there a viable alternative? I tried researching here in the forum, but I can't find a solution. Many thanks in advance for your support.

 

[XOR LX]

Hi.

An array formula per se does not necessarily equate to a large burden on calculation. It would depend largely on the particulars of that array formula, i.e. the number of instances of that formula within the worksheet and, importantly, the size of the range being queried.

On the latter point, I've seen many users make the mistake of using references which extend way beyond that which contains the data, perhaps reckoning that, with a potentially-expanding dataset, they are merely "covering their bases". However, this is an unfortunate belief indeed, since, for each additional cell referenced, extra calculation will be required.

And that applies whether those additional cells are technically beyond the last-used cells in those ranges or not. With some functions, e.g. COUNTIF(S)/SUMIF(S), you can get away with referencing entire columns with no detriment to performance, though not with functions which operate over arrays, such as AGGREGATE, SUMPRODUCT, and any construction requiring CSE.

And worst of all, of course, is the use of entire column references within an array formula: if, for example, you only have data extending as far as row 1000, then, by referencing an entire column's worth of rows, you are effectively forcing Excel to calculate more than one million rows than are actually necessary, resulting in an astonishingly resource-heavy formula. And that's just for one instance of that formula.

I do not know if these bad practices are relevant to your situation, since you did not give any examples of the formulas in your workbook, though perhaps it's something for you to think about, if not now then in future.

Regards

 

[cgrynberg]

Thanks for the input. Unfortunately in my case, because I have hundreds of names and multiple variables (the numbers and letters), even if I could reduce the range it is still too heavy. I tried in 50 lines and it couldn't support it already. I didn't paste the array formulas because I need another way of doing these calculations. I have also replaced all vlookups with Index/match to reduce the size, but I can't figure out how to do this summary/calculation. Thanks again

 

[Aladin Akyurek]

What does Mar/17 means? How do you get the figure of 4 for John and 1?

 

[XOR LX]

I didn't paste the array formulas because I need another way of doing these calculations.

But couldn't we at least see said formula so as to be able to:

a) Suggest why your current set-up is struggling
b) Offer alternatives

?

Regards

 

[cgrynberg]

Sorry, my mistake... This is the start date and the end date, it should be Mar/17. The user selects start date (in this case should be Mar/16) and the spreadsheet enters 01-Mar-2016. Mar/16 also selected for end date, so the spreadsheet enters 31/Mar/2016. Then I need to count how many 1, 2 and F there are for each user for the month of March. In this case, John has 4 number 1, Oliver 3 Number 2 and Mac 5 letter F. I will try to fix the post now... Let me know if still not clear, thanks!

 

[Aladin Akyurek]

Sorry, my mistake... This is the start date and the end date, it should be Mar/17. The user selects start date (in this case should be Mar/16) and the spreadsheet enters 01-Mar-2016. Mar/16 also selected for end date, so the spreadsheet enters 31/Mar/2016. Then I need to count how many 1, 2 and F there are for each user for the month of March. In this case, John has 4 number 1, Oliver 3 Number 2 and Mac 5 letter F. I will try to fix the post now... Let me know if still not clear, thanks!

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​		27-Mar-16​	28-Mar-16​	29-Mar-16​	30-Mar-16​	31-Mar-16​	1-Apr-16​
2​	John	1​	1​	1​	1​
3​	Oliver			2​	2​	2​	2​
4​	Mac	F	F	F	F	F	F
5​
6​
7​
8​	Select Start Date:		Mar-16​	Mar-16​
9​	Select End Date:		Mar-16​	Mar-16​
10​		1​	2​	F
11​	John	4​	0​	0​
12​	Oliver	0​	3​	0​
13​	Mac	0​	0​	5​

If C8 and C9 house true dates, that is, 1-Mar-2016 and 31-Mar-2016, use these cells. Otherwise:

D8:

=EOMONTH(C8-1,0)+1<strike></strike>

D9:

=EOMONTH(C9,0)<strike></strike>

In B11 just enter, copy across, and down:

=COUNTIFS(INDEX($B$2:$G$4,MATCH($A11,$A$2:$A$4,0),0),B$10,$B$1:$G$1,">="&$D$8,$B$1:$G$1,"<="&$D$9)<strike></strike>

 

[cgrynberg]

Thank you very much Aladin, exactly what I was looking for! Appreciate it!

 

[Aladin Akyurek]

Thank you very much Aladin, exactly what I was looking for! Appreciate it!

You are welcome.

 

[cgrynberg]

Trying to do an additional count on the same table from above. Here is the deal:

The inputs for this table (the 1,2,F…) come from another table (named Type, in a column named Number. The user can add whatever code he wants there and expand the number of Codes. I have data validation in this table, so only the codes entered in Type[Number] are allowed here. So for example, this table could contain only 3 codes as above (1,2,f) or many more (e.g. 1,11,G_12,EUR,GEN_A)…

I just want to count the total number of cells that are filled with any of these values from table Type[Number], while also condition for the names and date ranges, just as above. So on Aladin’s formula I would basically like to replace the B$10 reference with the values entered in table Type[Number].

Sorry I can’t get it on my own, and hoping it's a very simple answer... Many thanks again