0

I have excel sheet with 3 subjects: Customer, Data and Product. I want to calculate how many product for each customer, and I used "COUNTIFS" formula, the problem is customer row is merged, so when I use "COUNTIFS" formula it's doesn't work. can you help me please with this. Excel sheet attached.

File link: https://easyupload.io/5h2hou

Image

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
Mohsen
  • 13
  • 1
  • 5

1 Answers1

1

Although it is strong recommended to avoid merged cells where you need calculation using formulas. It cause too many problems. Still you can apply some tricks to achieve your goals. For your case you can try below formula-

=COUNTIFS(INDIRECT("C"&MATCH($F2,$A:$A,0)&":C"&MATCH($F2,$A:$A,0)+5),G$1)

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • You have to adjust `5` based on your data starting row. – Harun24hr Feb 28 '21 at 09:10
  • Thanks a lot, what about count unique value? I mean just assume that after Product columns there is Product Id, and I want just count unique Id and skip duplicate id. e.g. count how many Books for mike with unique id – Mohsen Mar 08 '21 at 11:04
  • 1
    The `5` doesn't depend as much on the starting row as on the size of the merged cells. If any of the merged cells cover more than 6 rows, this will fail. Use with caution. Also one can avoid the `INDIRECT` by using `INDEX`. – BigBen Dec 05 '22 at 18:15