I need formula (in column C) that will count same bill# from column H. So Nick, Marc, and Lucas are on the same bill#, that's total count of 3 for Nick (or Lucas) (column A). Gee and Lucas are on the same bill#, that's total count of 2 for Gee (column A). Matt and Kim are on the same bill# and that's total count of 2 for Matt (column A). What formula to use? Note that values in A, B, C columns and values in F, G, H column are separate sheets.

I need formula (in column C) that will count same bill# from column H. So Nick, Marc, and Lucas are on the same bill#, that's total count of 3 for Nick (or Lucas) (column A). Gee and Lucas are on the same bill#, that's total count of 2 for Gee (column A). Matt and Kim are on the same bill# and that's total count of 2 for Matt (column A). What formula to use? Note that values in A, B, C columns and values in F, G, H column are separate sheets.

• excel
• excel-formula

• Why not simply add =COUNTIF(H2:H8,H2:H8) at column I so you have all your results at once. – P.b Commented Feb 13 at 19:27

1 Answer 1

Here is one way to accomplish the desired output using one single dynamic array formula:

=MAP(A2:A5,LAMBDA(x, FILTER(COUNTIF(H2:H8,H2:H8),F2:F8=x)))

Alternatively, without using a LAMBDA() helper function;

=XLOOKUP(A2:A5,F2:F8,COUNTIF(H2:H8,H2:H8),"Oops Not Found!!")