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?

Solution 1:

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"))

Solution 2:

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.