Excel: If name in column A matches name in column B, then count number of times text "on time" is present in column C

0
votes

Column A: list of names Column B: list of names (not in same order as column A) Column C: text "on time" or " Late" Column D: formula entered here - If name in column A matches name in column B, then count number of times text "on time" is present in column C

I have tried sumif, sumifs, countif, countifs, sumproduct, to no avail. Prefer not to use VBA if at all possible. But will if that is the only solution.

1
excel-formula
Can you post what you have already tried? - bolt19
Can you post some example data? - hypercrypt
=COUNTIFS(A:A,B:B,C:C,"on time") =SUMPRODUCT(C3:C25,--(A4:A48),B4:B25) =SUMIFS(C3:C25,B3:B25,A3:A48) =SUMIF(A3:A48,B3:B25,C6:C25) The countifs is the one most closely working for me. It does return a value just not the correct count. - Dee
I figured it out. Thank you for wanting to help. The countifs formula just needed tweeking. Changed the reference of B:b first, then A:A and success! - Dee

1 Answers

0
votes

This will find the cell A1 in column B, then count the number of times "on time" is present in Column C if it's found:

=IF(ISERROR(VLOOKUP(A1,B:B,1,FALSE)), "",COUNTIF(C:C, "on time"))