• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2851
  • Last Modified:

How do I perform COUNTIFS on a range of merged cells in Excel 2010

Hi,

Is it possible to perform a COUNTIFS operation on a range of cells which include merged cells. For example, if i want to tally occurrences using two conditions: one on a range of merged cells, and the other on a range of single cells.

I've attached an example of the worksheet with my COUNTIFS formulae which don't tally correctly.

Thanks
Toco
Tests-and-Summary.xlsx
0
Tocogroup
Asked:
Tocogroup
2 Solutions
 
barry houdiniCommented:
I would advise you not to use merged cells for such calculations. Effectively merged cell B3:B5 is counted as B3 only and B4 and B5 don't exist so your COUNTIFS function only counts when the top cell in the range (in column C) matches.

regards, barry
0
 
andrewssd3Commented:
Unfortunately that's never going to work as only first of the merged cells contains the value.  I would suggest you replicate the values and perhaps use a pivot table to summarise as in the attached file
Tests-and-Summary.xlsx
0
 
TocogroupAuthor Commented:
Many thanks for both solutions. Hope you don't mind if I split the points as Barry explained the problem and Andrew provided an alternative solution.
Much appreciated to you both
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now