Countif/Sumif Type formula

nmawj

New member
Joined
Apr 6, 2015
Messages
1
Reaction score
0
Points
0
idnumscannerbasecount
100142123J11099
100142123547808
100142123H11219
100142123750198
100141240J1134
100141240
100141240H11464


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!!
 
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,
 
You don't need the &""

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

will suffice
 
Back
Top