Count number of dependents per employee

Hi
are the dependents listed only once, or are they duplicated? Also, does every dependent have a first name in column G and last name in column F?
If both are true, then counting the number of times an employee's name is in column A and there is a value in column F or G should count dependents for that employee.

if so, a formula like this might work for the employees whose name is in A2:

Please Login or Register  to view this content.

If that doesn't work, could you upload a dummy approximation of your sheet with made-up names so we can see its configuration (obviously it contains confidential information so you can't share the sheet itself).

Thanks for reviewing this. Yes the dependents are listed only once, but the employee (ID) will show up for each occurrence. Yes, every dependent will have a first and last name in columns F & G. The current formula only seems to look at the employee ID. Do you know how I could adjust that formula?

Hi
does this work better?

Please Login or Register  to view this content.

Count of the dependent, which is in column F & G. I re-uploaded with the intended output in column C.

Hi
if you're looking for a running total in the A column then this formula copied down should do it:

Please Login or Register  to view this content.

or, if there are no blank cells in column F, adapting FR's more economical formula:

Please Login or Register  to view this content.

=COUNTIFS(A$2:A2,A2,F$2:F2,"<>"&"")

yep thanks for the correction phuocam