Solved

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

Posted on 2013-01-07
3
1,869 Views
Last Modified: 2013-01-07
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
Comment
Question by:Tocogroup
3 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 38751438
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
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 250 total points
ID: 38752248
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
 

Author Closing Comment

by:Tocogroup
ID: 38752825
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now