Countifs Formula Problem!!!

[MarkReddell1]

Hello Excelers! I'm need help with this formula: =COUNTIFS(ContractsTable[[Lead]:[ButtonUp]],BP$4,ContractsTable[Year],D5,ContractsTable[Month],C5,ContractsTable[Contracts],1) PS: This range is across 1000 rows & 6 Columns. Is there a way use sumproduct on this??? Thanks 4 any help!!!

 

[Hany ali]

where's the file ?!!!

 

[MarkReddell1]

On my laptop!

 

[MarkReddell1]

I fixed it!!! =SUMPRODUCT(--(ContractsTable[[Lead]:[ButtonUp]]=ContractsTable[[#Headers],[Dylan]])*(ContractsTable[Contracts]=1)*(ContractsTable[Year]=D5)*(ContractsTable[Month]=C5)) Thanks 4 help!!!

 

[Peter Bartholomew]

@MarkReddell1

Is there a way use SUMPRODUCT on this?

Clearly the answer is yes; but why? COUNTIFS is 3 or more times faster than SUMPRODUCT and can accept arrays in the criteria fields in order to give the results as a single array. The latter point is important feature of exploiting dynamic array solutions.

 

[MarkReddell1]

I agree! However, I couldn't get countifs to work across many columns & rows @ the same time!!! I got sumproduct to work, however, my sure that if there is a way for countifs to work, I would use it instead!!!

 

[Peter Bartholomew]

You are probably better off with SUMPRODUCT in that case. Although it is possible to use COUNTIFS over a 2D array, all the criteria ranges must have the same number of rows and columns.

 

[Hui]

Mark

Everything that Peter has said is valid except that I suspect you are checking multiple fields which sumproduct allows, but Countif/s doesn't

The problem is that by using Structured List References, which is good practice, without a file we cannot assist you as we cannot see what is going on with the fields

Typically attaching a small sample file to the post is enough to assist us assist you, which is what Hany Ali was asking

 

[MarkReddell1]

Thanks Again for ALL the wealth of knowledge here on Mr Excel message board!!! Merry Christmas to ALL!!! PS: I use to be able to paste a sample of data, however, its not working currently! Any advise is welcome!!!

 

[MarkReddell1]

Also, do know to call a macro with a delete keyboard stroke??? Reason being, is a have a formula in a cell that also can overridden with a manual entry. However, I would like to restore the formula into cell with the delete key!

 

[NARAYANK991]

Hi ,

Can you please upload your workbook ? In the absence of your workbook , I for one am not able to understand your requirement or your problem.

Narayan

 

[Peter Bartholomew]

I think tracking individual keystrokes is possible with forms but less so with worksheet data. An alternative might be to monitor worksheet changes and reinstate the formula if a blank is detected.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [output]) Is Nothing Then
        If Target.Item(1).Value = vbEmpty Then
            Target.Item(1).Formula = "= REPT(@letter, 6)"
        End If
    End If
End Sub

where 'output' is a named range and "= REPT(@letter, 6)" is a sample formula.

 

[Saul Espinoza]

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 slicers linked 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)
{"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 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

 

[AliGW]

You will need to start your own thread for this.