CODEWITHSUNDEEP
excelexcel-formulavlookupcountif
Fah
Fah

Reputation: 203

Is there a excel formula to count how many times an ID appears in a column when the value in another column is YES?

I have a table with 5 columns. Column C with - ID numbers that need to be checked. Column D with - name of the person who should check the ID. Column E - say if it was checked or not by "YES" or "NO"

However, I need a formula on Columns A and B to show who did the last check and how many time the ID was checked already.

On column A need to appear the name of who did the last check on a specific ID Taking in consideration the information On columns C ID number, D = person and E = is it was checked or not.

On column B I need to count how many times the ID on the column C appear, but only if it YEs on column E. =COUNTIFS(F:F,F152)+COUNTIFS(J:J,J152="Yes")

enter image description here

example in the image the ID number 59461 appear on C2, C6 and C8 and E2, E6 is YES and only E8 the value is NO

what would the the best approach to get the result I need for column A and B?

Upvotes: 0

Views: 786

Answers (1)

JvdV
JvdV

Reputation: 75900

I guess in A2 you could use:

=LOOKUP(2,1/((C:C=C2)*((E:E="Yes"))),D:D)

In B2 use:

=COUNTIFS(C:C,C2,E:E,"Yes")

Not sure it's what you are after, let me know.

Upvotes: 1

Related Questions