Results 1 to 3 of 3

Thread: Countif/Sumif Type formula

  1. #1

    Question Countif/Sumif Type formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    idnum scanner base count
    100142123 J11099
    100142123 547808
    100142123 H11219
    100142123 750198
    100141240 J1134
    100141240
    100141240 H11464


    Hi I need help with above table.

    Based on idnum(first column) I need a count of scanners associated to that ID.

    So ideally, I would see 2 in column "Count" for the first ID
    SUMIF is set up perfectly in terms of range,criteria,and sum range but Countifs does not have the same setup.

    Please let me know if this is possible.

    thanks!!

  2. #2
    Good afternoon,

    You can use COUNTIFS.

    = COUNTIFS ( IDNUM Range , single IDNUM on the same line [i.e. A2] , SCANNER Range , "<>"&"")

    Assuming the data starts in A1, it would look something like, in D2:

    =COUNTIFS(A:A,A2,B:B,"<>"&"")

    This would count any scanners that are not blank and have the same IDNUM as the corresponding value in column A. The ranges could also be shorted from the whole column (i.e. A2:A20 instead of A:A).

    Best of luck,

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    You don't need the &""

    =COUNTIFS(A:A,A2,B:B,"<>")

    will suffice

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •