• 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.

Formula to count the occurrence of a value for a particular name or sting?

Vijaychitra

New Member
Attached the spreadsheet.
Raw table - provides information - name & score
Expected table - Need to find the count of score occurrence for a particular name.

I'm using count, able to get the count, but need to extend with name check.
 

Attachments

AlanSidman

Well-Known Member
In H6 =COUNTIFS($D$6:$D$19,">90",$C$6:$C$19,G6) and copy down.

In I6 =COUNTIFS($D$6:$D$19,"<91",$D$6:$D$19,">69",$C$6:$C$19,$G6) Use this example for the remainder of the cells
 

Vijaychitra

New Member
Thanks Alan. However when used the below formulas in VBA macro, unable to execute

=COUNTIFS('Sheet2'!L2:L1000,">90",'Sheet2'!F2:F1000,'Sheet1'!B5)

=COUNTIFS('Sheet2'!L2:L1000,">=3",'Sheet2'!L2:L1000,"<=5",'Sheet2'!F2:F1000,'Sheet1'!B5)
 

AlanSidman

Well-Known Member
Countifs is not a VBA function. If you wish to use countifs in VBA, you need to preface with Application.WorksheetFunction(Countifs(....

and you need to show your ranges with the Range Object, ie. Range("L2:L1000")

Additionally, in your original post, you made no reference to this need as a VBA. You requested a formula.
 

vletm

Excel Ninja
Vijaychitra
If You're asking Excel-question then You should get Excel-replies.
If You're asking VBA-questions then You should get VBA-replies.

as written in Forum Rules:
Please post, new posts in the correct forums
 

Peter Bartholomew

Well-Known Member
Since the OP asked for a formula and not a macro, either VBA or TypeScript, I wrote a 365 solution
Code:
= LET(
    sep,      FIND("-", ranges),
    upper,  --RIGHT(ranges,LEN(ranges)-sep),
    lower,  --LEFT(ranges, sep-1),
    distinct, UNIQUE(Name),
    COUNTIFS(
      Name, distinct,
      Score,  "<=" & upper,
      Score, ">" & lower))
More a matter of familiarisation with 365 techniques than something th op can use.
76555
 
Top