excel - Count Same Values Based on Another Value in Another Column - Stack Overflow

admin管理员组

文章数量:1415684

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

Share Improve this question Follow asked Feb 13 at 18:16 vujkevujke 49588 silver badges1414 bronze badges 1

• 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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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!!")

---

本文标签： excelCount Same Values Based on Another Value in Another ColumnStack Overflow