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
TocogroupAsked:
Who is Participating?
 
andrewssd3Connect With a Mentor Commented:
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
 
barry houdiniConnect With a Mentor Commented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.