VBA CountIfs Help

[ayubalia1]

Hi, need your help urgentlyI'm working on a project need your help on Countifs functionPositiveScr = Application.WorksheetFunction.CountIfs(Sheet2.Range("F:F"), "AMER", Sheet2.Range("F:F"), "EMEAR")orPositiveScr = Application.WorksheetFunction.CountIfs(Sheets(2).Range("H:H"), "EMEAR", "&", "AMER")My code actually contains many Criterias and Ranges, however the above simple code i need your helpdepends on few conditions I may need to count only EMEAR or AMER or both together, Kindly help me

 

[Domenic]

To count the number of times Column F contains "AMER" or "EMEAR", try...

With Application.WorksheetFunction
    PositiveScr = .CountIf(Worksheets("Sheet2").Range("F:F"), "AMER") + .CountIf(Worksheets("Sheet2").Range("F:F"), "EMEAR")
End With

An alternative would be to use the Evaluate method...

PositiveScr = Evaluate("SUM(COUNTIF('Sheet2'!F:F,{""AMER"",""EMEAR""}))")

Note, however, the Evaluate method has a 255 character limit.

Hope this helps!

 

[ayubalia1]

Hi Domenic,
Thanks for your help on this, unfortunately i'm getting results as zero

Let me explain my request for help
I have Sheet 2 with (Data) and sheet 3 with Output Dashboard(Metrics)

 PoitiveScr = WorksheetFunction.CountIfs(Worksheets("Data").Range("FM:FM"), Worksheets("Metrics").Cells(3, N), Worksheets("Data").Range("B:B"), Worksheets("Metrics").Range("D1"))

In this code Last Criteria Range(D1) is the value of DropDown from sheet 3, if Dropdown value is "EMEAR" then results should come as per EMEAR, if AMER then AMER results, if the Dropdown value is "Overall" then code should calculate both "EMEAR" & "AMER" PFA Screen shot

Many Thanks in Advance for help

 

[Domenic]

In that case, try...

PoitiveScr = WorksheetFunction.CountIfs(Worksheets("Data").Range("FM:FM"), Worksheets("Metrics").Cells(3, N).Value, _
    Worksheets("Data").Range("B:B"), IIf(Worksheets("Metrics").Range("D1").Value = "Overall", "*", Worksheets("Metrics").Range("D1").Value))

 

[ayubalia1]

Hi Domenic,

Thank you so much for your help it works me perfect, just curious to ask you how this code works "IFF".

Thanks again for help

 

[ayubalia1]

Also will you help me how to assign multiple values in one Range,
Example I have sheet which contains Customer satisfaction scores,
Using CountIfs formula with many criterias a column contains scores "1 or 2 or 3 or 4 or 5 or 6" scores equals to Neutral and 7 or 8 equal to Neutral and 9 or 10 equals Positive.
I need 3 variable outputs values as Positive numbers or Neutral numbers or Negative numbers.
I'm very beginner to VBA with so many ideas and less knowledge on VBA, please help me which can give me strength to learn VBA
Thanks in Advance

 

[Domenic]

Hi Domenic,

Thank you so much for your help it works me perfect, just curious to ask you how this code works "IFF".

Thanks again for help

The IIF function is similar to the worksheet IF function, and has the following syntax...

[COLOR=#000000][FONT=&quot]IIf[/FONT][/COLOR][COLOR=#000000][FONT=&quot]([/FONT][/COLOR][I]expr[/I][COLOR=#000000][FONT=&quot], [/FONT][/COLOR][I]truepart[/I][COLOR=#000000][FONT=&quot], [/FONT][/COLOR][I]falsepart[/I][COLOR=#000000][FONT=&quot])[/FONT][/COLOR]

If expr evaluates to true, truepart is returned. Otherwise falsepart is returned.

 

[Domenic]

Also will you help me how to assign multiple values in one Range,
Example I have sheet which contains Customer satisfaction scores,
Using CountIfs formula with many criterias a column contains scores "1 or 2 or 3 or 4 or 5 or 6" scores equals to Neutral and 7 or 8 equal to Neutral and 9 or 10 equals Positive.
I need 3 variable outputs values as Positive numbers or Neutral numbers or Negative numbers.
I'm very beginner to VBA with so many ideas and less knowledge on VBA, please help me which can give me strength to learn VBA
Thanks in Advance

Since this is a new question, please start a new thread, and ask your question there. I would also suggest that you include a small sample of your data, along with your expected result.

 

[ayubalia1]

Thanks Domenic I appreciate your help on this
I have created new Thread: Help Reqired on VBA Count Ifs Foumula using multiple Criterias

When you have spare time please help on this too
[h=1][/h]