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:

[table="width: 500, class: grid"]
[tr]
[td][/td]
[td]27/Mar/16[/td]
[td]28/Mar/16[/td]
[td]29/Mar/16[/td]
[td]30/Mar/16[/td]
[td]31/Mar/16[/td]
[td]01/Apr/16[/td]
[/tr]
[tr]
[td]John[/td]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Oliver[/td]
[td][/td]
[td][/td]
[td]2[/td]
[td]2[/td]
[td]2[/td]
[td]2[/td]
[/tr]
[tr]
[td]Mac[/td]
[td]F[/td]
[td]F[/td]
[td]F[/td]
[td]F[/td]
[td]F[/td]
[td]F[/td]
[/tr]
[/table]

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

[table="width: 500, class: grid"]
[tr]
[td][/td]
[td]1[/td]
[td]2[/td]
[td]F[/td]
[/tr]
[tr]
[td]John[/td]
[td]4[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Oliver[/td]
[td][/td]
[td]3[/td]
[td][/td]
[/tr]
[tr]
[td]Mac[/td]
[td][/td]
[td][/td]
[td]5[/td]
[/tr]
[/table]

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.

 

[Aladin Akyurek]

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

Try...

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

 

[cgrynberg]

Works perfectly! Thank you very much again, this was awesome!

 

[Aladin Akyurek]

Works perfectly! Thank you very much again, this was awesome!

You are welcome.