0

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.

This is the representation

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
2

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
1

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.

enter image description here

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.

Adjust the Data Range as your need.

Hope the help you, I've posted after is been tested by me.

  • @Xhaltr, I've used the best possible method to track down the activities falling in different dates in 2 segments because for better understanding. – Rajesh S Nov 22 '17 at 11:30
  • You answer is interesting, but I don't understand how that's different from mine ? Could you be more precise ? – xhaltar Nov 23 '17 at 8:34
  • My solution is exactly what has been asked by you. I found it's the best method I can suggest you to Tabulate the Projects falling within the Dates. If you need other than this, just post the Screen Shot of the Output Format I'll create other one. – Rajesh S Nov 23 '17 at 9:08

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.