SteveDB1 wrote...
My equation is as follows:
sumif(a1:z3,aa1,a4:z7)
I'm checking the range of a1 to z3 for all occurrences of the contents of
aa1. Once those instances are found, I want to sum all of the values within
the range from a4 through to z7, under the specified headings.
As you see, I've made my sum range 4 rows tall. I've done this deliberately,
as my data set has merged cells, that are 4 rows tall (part of our internal
requirements that have worked quite well thus far).
....

Well, your blissful period is at an end. Merged cells screw up nearly
all formulas and data manipulation operations.

Occasionally, within the data set, there are rows that are not merged, and
have more than one row/cell with a value in it.
I.e., c4 = 2; c5 = 12; c7 = 2. Then a little further down the row, t5 = 32;
t6=1.

Meaning C6 is merged with C5 in your first example? As for your second
example, if you have a separate T5 cell, you MUST have a separate T4
cell, but T6 would presumably be merged with T7.

My problem is that sumif is not counting all values. It only appears to be
counting c4's cell value.

Does A1:Z3, the criteria range, also contain merged cells?

However, I have found that sum() will in fact count all of the four
individual cells. Unfortunately, due to the data requirements, I'm unable to
use just the sum function.

IOW, you need conditional sums.

Does anyone know:
1- why this does not work?

Yes. SUMIF, along with nearly all other built-in Excel functions,
chokes when fed merged cells.

Is there some restriction that only allows for a single row accounting?

Nope. Unmerge the cells and ensure that the 1st and 3rd arguments to
SUMIF are the same size and shape, and SUMIF works acording to specs.
Feed SUMIF 1st and 3rd arguments that are different size/shape, and
it'll return garbage.

You're misusing SUMIF. It's quite likely it's IMPOSSIBLE for you to
meet all your design specs. So you're going to have to decide which
are more important.

2- how to make it work?

1. By unmerging cells.
2. By passing 1st and 3rd range arguments to SUMIF that are the same
size.

There may be work-arounds for what you're trying to do, but you
haven't provided enough information to give any specific assistance
other than to state why your current approach is doomed to failure.

Does A1:Z3 contain merged cells? If so, you're going to need to
explain how you want the calculations to work for every combination of
merged cells in each column. And that assumes each column's
conditional sum would be independent of the other columns.