[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2013-01-07
3
Medium Priority
?
2,697 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
[X]
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
3 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 1000 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 1000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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 …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

650 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