Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2013-01-07
3
2,115 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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