# Excel Matrix Condition - Is there a faster way?

I'd like to get some data depending on multiple entries.

I'm using the "NB.SI.ENS()" which is "COUNTIFS()" in English if I'm not mistaken.

I have multiple entries, which would represent a 3D array since I'm using 3 Sources. Those Sources are an Activity, a Project, and a Date.

For each one, I need to count the triplets (Activity, Project, Date). My exemple in the Image shows 3 for the Number of Tests in Project 1 on the 10th Month.

With two Sources, the COUNTIFS() functions work okay. I can do this with as much COUNTIFS() as there are intersection between Rows and Columns from the two Sources (for instance, Activities, and Projects)

With 3 entries, I need to make as much 2D arrays as there are datas in the third array.

I've chosen the Dates as the 3rd array, so I have 12 2D arrays.

This uses a lot of computation time from Excel when I'm getting over 200 ligns of data.

Is there a better way to do that?

• If I'm not mistaken you are trying to count Project wise entries for those 3 Development, Test & Mobilization,,, write ?? One Advice the Date should not use as criteria BCoz all rows have same date 01/10/2017. – Rajesh S Nov 22 '17 at 7:02
• Your are right, my image is not explicit enough. There are of course rows with different dates. I've updated the exemple with other dates (November, December) – xhaltar Nov 22 '17 at 9:47

I have a very dirty solution, but I have tried and can get instant result for 600 rows of data.

I have screen cap my setup for you (https://image.ibb.co/kqxvYR/tmp.png)

Basically, I have concatenated the three text together using a symbol, and only done COUNTIF for that column

Step by step:

1. Concatenate the 3 texts
1. Using this command [=CONCAT(\$A2," | ",\$B2, " | ",\$C2)] for D2 (increase the number by 1 each row)
2. For instance : A2 = Test, B2 = Project 1, C2 = 01/10/2017
3. Now I got: Tests | Project 1 | 01/10/2017
2. When you need to search: Paste the wanted 3 items in separate place

1. For instance: F10 = Modelisation, G10 = Project 2, I10 = 01/10/2017
2. Use 1.1 Command again [=CONCAT(\$F10," | ",\$G10, " | ",\$I10)] for F12
3. Now F12 will look like this Modelisation | Project 2 | 01/10/2017
4. You can get your result using this a much more simple COUNTIF Command: =COUNTIF(D2:D14,F12)
3. Now you might think that giving this ugly test Modelisation | Project 2 | 01/10/2017 is not accpetabl

1. You can use hide it using Excel formatting magic
2. Drag over all the D column ugly text
3. Right click > Format Cell
4. Then Number tab > Custom
5. Type in ;;; in the textbox
6. Then the ugly text is not visible to user anymore

Hope this helps you!

• Yes, this helps a lot. For 200 rows of data, I went from 10 seconds of computing time to 3 seconds – xhaltar Nov 23 '17 at 8:36
• Glad it helps ! – Ng Sek Long Nov 23 '17 at 10:29
• @NgSekLong, Solution is a Solution ultimately,, and you have done it in your way. Helper column is one of useful tools but many user don't like it. But I always appreciate the effort. – Rajesh S Nov 25 '17 at 9:12

After analysing your Screen Shot and the Query I've created a Solution which is Counting all three activities falling on different dates.Check this Screen Shot.

Data Range is A434:D459.

First Formula in B449.

=COUNTIFS(\$A\$434:\$A\$446,"="&\$A449,\$B\$434:\$B\$446,"="&\$A\$448,\$C\$434:\$C\$446,"="&B\$448)

Sceond Formula in B453.

=COUNTIFS(\$A\$434:\$A\$446,"="&\$A453,\$B\$434:\$B\$446,"="&\$A\$452,\$C\$434:\$C\$446,"="&B\$452)

Third Formula in B457.

=COUNTIFS(\$A\$434:\$A\$446,"="&\$A457,\$B\$434:\$B\$446,"="&\$A\$456,\$C\$434:\$C\$446,"="&B\$456)

Drag all Formulas 2 Columns Right then 2 Row Down.