How to use multiple array in one countif formula

I need to find the number of the cells in a table, when all conditions are met. So I wrote the following formula:

=SUM(COUNTIFS(Master_tbl[Erfassungsdatum],">=01.01.2017",Master_tbl[Erfassungsdatum],"<=31.01.2017",Master_tbl[Auftragsart],{"YAEL","YAFD","YALP","YAPL"},Master_tbl[IH-Leistungsart],{"T01","T02","T03","T04","T35"},Master_tbl[P/AP],"AP"))

But the result is less than real. When I use only one set of array, the result is correct, but when I use two sets of array, the result is incorrect.

Can anyone help me to find my mistake please?

Dio

Posted 2017-11-07T13:16:50.547

Reputation: 333

I'm not 100 percent, but i don't think you can use the 2 arrays in one instance like this, i have tried before myself and got odd results. as a work round try and use a helper column to do the 2 countifs independently, and then a count if on those where there is matches, hope that helps – PeterH – 2017-11-07T13:20:43.010

@User91504: thanks for your comment. The problem is that there is no possibility to add a helper column, otherwise you are right. – Dio – 2017-11-07T13:24:29.110

@Dio Can you please show some screenshots? I do know what you are asking for is possible. If you provide me the example you are working with, I can provide you a solution that matches what you have better – Eric F – 2017-11-07T13:38:35.753

@Eric I added some screenshots, but I am not sure if it is clear, becaue the table is too large. – Dio – 2017-11-07T14:00:58.370

@Dio Thanks however it seems you have two answers below anyways which hopefully do help you out :) – Eric F – 2017-11-07T14:01:49.163

Answers

You can only do 2 arrays max and one needs to be separated with ; and the other ,. That way one is a vertical array and the other is a horizontal.

=SUM(COUNTIFS(Master_tbl[Erfassungsdatum],">=01.01.2017",Master_tbl[Erfassungsdatum],"<=31.01.2017",Master_tbl[Auftragsart],{"YAEL","YAFD","YALP","YAPL"},Master_tbl[IH-Leistungsart],{"T01";"T02";"T03";"T04";"T35"},Master_tbl[P/AP],"AP"))

Scott Craner

Posted 2017-11-07T13:16:50.547

Reputation: 16 128

Thanks Scott. I like this solution, but there is a challenge. In German language Excel we use ";" as ",". Now I don't know how to add "," in the second array, because the excel doesn't accept it. – Dio – 2017-11-07T14:44:44.977

@Dio - you can work that out like this: in a blank worksheet in cell A1 type =B2:D4 and then press F2 key followed by F9 key - you should see the two punctuation types used in your version, e.g. in mine I see ={0,0,0;0,0,0;0,0,0} – barry houdini – 2017-11-07T20:53:34.100

Great Barry. :) A good consult. This is the result ={0.0.0;0.0.0;0.0.0}. I have to use period "." rather than comma. Thank you so much. – Dio – 2017-11-08T13:21:44.563

Multiple arrays used as you have in your formula basically evaluate the formula by aligning the various arrays and using each set independently (i.e. YAEL+T01, YAFD+T02). This is not what you are looking to do.

I'm sure there are other methods to achieve what you're looking to do but the one that sprang to mind was the use of Sumproduct. I'm not sure how long you've been using Excel, but for a long time Sumproduct was the best way to perform a multi criteria Countif. Taking your formula above and converting it we get:

=SUMPRODUCT(--(Master_tbl[Erfassungsdatum]>="01.01.2017"),--(Master_tbl[Erfassungsdatum]<="31.01.2017"),--(ISNUMBER(MATCH(Master_tbl[Auftragsart],{"YAEL","YAFD","YALP","YAPL"},0))),--(ISNUMBER(MATCH(Master_tbl[IH-Leistungsart],{"T01","T02","T03","T04","T35"},0))),--(Master_tbl[P/AP]="AP"))

ISNUMBER(MATCH...essentially converts what would be a multidimensional array (if you were doing the simple comparison of column = {array}) into a single dimension, which can then be combined with the other criteria comparisons.

I wasn't entirely sure what the format of your Erfassungsdatum column is so the comparison for those assumes a string value. If it's a date you'll want to adjust accordingly.

Nayrb

Posted 2017-11-07T13:16:50.547

Reputation: 528

Thanks Nayrb for your reply. I am not an old user of Excel :) but I am so eager to learn excel well. I tried your formula, but the result is Zero "0". I cannot understand why. I think I have to study that more deeply. – Dio – 2017-11-07T14:43:05.633

Is your date column actually a date? If so then you can wrap the "01.01.2017" in the DATEVALUE function. – Nayrb – 2017-11-07T15:03:15.870

You are right. The date is actualla a date, so with your new advice it works. Thank you so much. Just a simple question: Do you know if SUMPRODUCT accept more than two arrays? – Dio – 2017-11-08T13:32:25.440

SUMPRODUCT should be able to handle up to 256 arguments, and you could probably use arrays in every single one of those arguments (though I wouldn't recommend that many). – Nayrb – 2017-11-08T13:36:13.640