• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countifs 3 dimension array issue!

Saul Espinoza

New Member
Good PM, Excel lovers, greetings and I hope this message find you all safe and healthy!!!!
I need to mod bellow formula so the 2 linked slicers work dynamically on the visualization:
Please see the attached Excel file for reference:
I'd like to get this debug portion of the formula (2/3 dimension array) with comas;
{"CAS - D.Leg. 1057","D.Leg. 276","D.Leg. 728","Ley No. 30057","Locador de Servicios","PAC/FAG","Practicante / Secigrista"}" from Calculation!$A$4:$A$10 within below formula;
=SUM(COUNTIFS(OFFSET(INDIRECT("CONSOLIDADO["&$A21&"]"),,,COUNTA(INDIRECT("CONSOLIDADO["&$A21&"]"))),B$20,CONSOLIDADO[[Selecciona tu Régimen Laboral o condición de trabajo / servicio:]:[Selecciona tu Régimen Laboral o condición de trabajo / servicio:]],Calculation!$A$4:$A$10,CONSOLIDADO[[OFICINA]:[OFICINA]],Calculation!$H$5:$H$25))
The second criteria when debug (F9), must be linked to a range that when referenced it will have to return:
{"CAS - D.Leg. 1057","D.Leg. 276","D.Leg. 728","Ley No. 30057","Locador de Servicios","PAC/FAG","Practicante / Secigrista"}, separated with comas
and not:
{"CAS - D.Leg. 1057";"D.Leg. 276";"D.Leg. 728";"Ley No. 30057";"Locador de Servicios";"PAC/FAG";"Practicante / Secigrista"}, semicolons, which currently return. BTW I switch those semicolons into comas manually from the cell range above, and it work correct, as opposed to linked to a cell range.

So if there is an Excel function(s) Non-VBA that could be embedded that makes this range Calculation!$A$4:$A$10 to return comas, I will appreciate very much your support. I already tried substitute, replace, etc and no luck. Best regards
 

Attachments

  • modelo Donany.xlsx
    149.6 KB · Views: 4
With English localisation
={"CAS - D.Leg. 1057";"D.Leg. 276";"D.Leg. 728";"Ley No. 30057";"Locador de Servicios";"PAC/FAG";"Practicante / Secigrista"}
is a column array constant. The easiest way to handle it might to be to assign a defined name (e.g. list) to refer to it.
Then, to create a row array use
=TRANSPOSE(list)
rather than attempting to edit a formula within a text editing formula. To get an edited text string to work as a formula requires it to be a parameter of the EVAL function, to be used within a named formula.
 
With English localisation
={"CAS - D.Leg. 1057";"D.Leg. 276";"D.Leg. 728";"Ley No. 30057";"Locador de Servicios";"PAC/FAG";"Practicante / Secigrista"}
is a column array constant. The easiest way to handle it might to be to assign a defined name (e.g. list) to refer to it.
Then, to create a row array use
=TRANSPOSE(list)
rather than attempting to edit a formula within a text editing formula. To get an edited text string to work as a formula requires it to be a parameter of the EVAL function, to be used within a named formula.
Thank you very much Sen Sei Peter!!! You Rock!!!!!! Thanks to you now I understand these concepts; "column array constant" and "rows array constant", The transpose function did the trick!!!
 
Back
Top