CODEWITHSUNDEEP
excelif-statementcountifsumifsconsuming
Mohsen
Mohsen

Reputation: 23

Excel COUNTIFS with merged cell

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

Upvotes: 1

Views: 9225

Answers (1)

Harun24hr
Harun24hr

Reputation: 37125

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

Upvotes: 1

Related Questions