sumif with "broad" sum range

[SteveDB1]

Hi all.
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).
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.
My problem is that sumif is not counting all values. It only appears to be
counting c4's cell value.
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.
Does anyone know:
1- why this does not work?
Is there some restriction that only allows for a single row accounting?

2- how to make it work?
I noticed on one post that one party used an index function that allowed for
varying cells.
I.e.,

One way

=SUM($A$1:INDEX(A:A,D1))

where A1 is the first row and D1 holds the last row number

Thanks for your help, in advance.
Best Regards.

 

[Harlan Grove]

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.

 

[SteveDB1]

Hi Harlan, how've you been?
My responses will be intermingled with yours-- below.

....

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

so I've noticed.....

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.

Not necessarily.
I just chose those as random choices. If I needed to have a dual value cell,
say I had a merged 4 row set at c4:c7, and I required just two values, I'd
unmerge all four cells, and only have the two values in c4, and c5, or any
combination.

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

Yes, they are they are individual columns, and 4 row, merged "sets."
Everything in the specified area.

IOW, you need conditional sums.

So it would appear. I need to look for a predetermined item-- criteria. Over
a range of merged cells-- criteria range. I then need to sum the 1, 2, 3, or
4 values within the individual columns, and merged, or unmerged cells.
In testing sumif, I unmerged the source (sum range) cells, and placed 3
numbers-- one in each cell. Sumif only counted the first value. It ignored
the other two. In spite of my having told it to look (sum range) from a4
through to z7. I "assumed (yes, I know, can't ever do that)" that it'd read
everything within the range I'd given, regardless ofthe status of the
groupings of cells, or their configuration.

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

That's good to know. Thanks.

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.


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.

Ok, based on what I've given, what else are you able to identify that I'd
need to provide to you? I've tried giving all that I can readily see as being
pertinent. Anything more and I'd need to give you the worksheet so you could
go through it line by line.

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.

Yes, it does.
I'd like to be able to have a function that will allow me to look through a
specific set of merged cells, for a specific criteria. These are all merged
cells, but individual columns (no columns are merged, only rows- 4 rows
tall).
Once the criteria are matched, and return a TRUE, I then want to look
through a range of cells-- a4:z7, regardless if they're merged or not, and
sum the contents of all 4 rows-- regardless if merged or not. Columns will
never be merged. Or if they were to be, I'd make the necessary modifications
to be counting individual rows, with merged columns-- a transposed layout (a
non-issue here).

Thanks.

 

[Harlan Grove]

Yes, they are they are individual columns, and 4 row, merged "sets."
Everything in the specified area.

....

Um, A1:Z3 contains only 3 rows. So do you mean A1:A3 are merged into
one apparent cell (A1), B1:B3 are merged into one apparent cell (B1),
etc for columns C through Z?

If so, this becomes much simpler.

=SUMPRODUCT((A1:Z1=AA1)*A4:Z7)

 

[SteveDB1]

Hi.
again-- my responses are mixed with your below.

....

Um, A1:Z3 contains only 3 rows. So do you mean A1:A3 are merged into
one apparent cell (A1), B1:B3 are merged into one apparent cell (B1),
etc for columns C through Z?

ugh... sorry. Actually, those are my source for the crtieria range-- in this
specific case.

If so, this becomes much simpler.

=SUMPRODUCT((A1:Z1=AA1)*A4:Z7)

really!?
Sumproduct will handle this? It's a far simpler use of it than I'm used to.
So, in this specific case, my sumproduct formula would be
=sumproduct((a1:z3=aa1)*(a4:z7))
where, as you'd stated, my merged cells are a1:a3 are the "apparent cell"
a1, and b1:b3 are the "apparent cell" b1, etc.... through to column z.

Now, my "sum range" would be the a4:z7. Where a4:a7 would be the "apparent
cell" a1. And in some cases, there would be some cells that would in fact be
unmerged. This will count all of the column's contents, even though there are
multiple cells, unmerged?
I'll check it out.
Thanks.

 

[SteveDB1]

Mornin' Harlan.
=SUMPRODUCT((A1:Z1=AA1)*A4:Z7)

Ok, I tried it, and I did not get the response needed. And this was on a
merged cell, with a single value.
In using the check calculation utility in office 2007, it gave the following
response.
(10 false's, 1 true, 25 false's)*(41-n/a's)
I expected to find only one true in the first portion. I did not expect to
find the n/a values. There is suppose to be a single value which will return
a -2.76, that matched the location of the one true.

 

[Harlan Grove]

=SUMPRODUCT((A1:Z1=AA1)*A4:Z7)

Ok, I tried it, and I did not get the response needed. And this was on a
merged cell, with a single value.

All right, some exhaustive specs would be nice.

I put the following in A1:R3, with underscores between columns, and
merged cells shown with # rather than numbers, so merged into the next
cell above it containing numbers.

0_1_0_1_0_1_0_1_0_1_0_1_0_1_0_1_0_1
0_0_1_1_0_0_1_1_#_#_#_#_0_0_1_1_#_#
0_0_0_0_1_1_1_1_0_0_1_1_#_#_#_#_#_#

I entered the following in A4:R7, all unmerged,

1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1
2_2_2_2_2_2_2_2_2_2_2_2_2_2_2_2_2_2
4_4_4_4_4_4_4_4_4_4_4_4_4_4_4_4_4_4
8_8_8_8_8_8_8_8_8_8_8_8_8_8_8_8_8_8

I entered the following formulas in A9:A10,

A9:
=SUMIF(A1:A3,0,A4:A7)

A10:
=SUMIF(A1:A3,1,A4:A7)

and filled A9:A10 right into B9:R10. Then I entered the following
formulas into S9:S10,

S9:
=SUM(A1:R3,0,A4:R7)

S10:
=SUM(A1:R3,1,A4:R7)

The results were

7_6_5_4_3_2_1_0_5_4_1_0_3_2_1_0_1_0_45
0_1_2_3_4_5_6_7_0_1_4_5_0_1_2_3_0_1_45

In **NONE** of these cases does the SUMIF include the value in row 7.
Take this as meaning that SUMIF will DISCARD values when its 1st and
3rd range arguments are different sizes/shapes. You need to specify
what these should have returned.

And then I merged rows in A4:A7 separately by column and got the
following results.

A4:R7:
1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1
#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#
4_4_4_4_4_4_4_4_4_4_4_4_4_4_4_4_4_4
8_8_8_8_8_8_8_8_8_8_8_8_8_8_8_8_8_8

A9:S10:
5_4_5_4_1_0_1_0_5_4_1_0_1_0_1_0_1_0_33
0_1_0_1_4_5_4_5_0_1_4_5_0_1_0_1_0_1_33


A4:A7:
1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1
2_2_2_2_2_2_2_2_2_2_2_2_2_2_2_2_2_2
#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#
8_8_8_8_8_8_8_8_8_8_8_8_8_8_8_8_8_8

A9:S10:
3_2_1_0_3_2_1_0_1_0_1_0_3_2_1_0_1_0_21
0_1_2_3_0_1_2_3_0_1_0_1_0_1_2_3_0_1_21


A4:A7:
1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1
2_2_2_2_2_2_2_2_2_2_2_2_2_2_2_2_2_2
4_4_4_4_4_4_4_4_4_4_4_4_4_4_4_4_4_4
#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#

A9:S10:
7_6_5_4_3_2_1_0_5_4_1_0_3_2_1_0_1_0_45
0_1_2_3_4_5_6_7_0_1_4_5_0_1_2_3_0_1_45


A4:A7:
1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1
#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#
4_4_4_4_4_4_4_4_4_4_4_4_4_4_4_4_4_4
#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#

A9:S10:
5_4_5_4_1_0_1_0_5_4_1_0_1_0_1_0_1_0_33
0_1_0_1_4_5_4_5_0_1_4_5_0_1_0_1_0_1_33


A4:A7:
1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1
#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#
#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#
8_8_8_8_8_8_8_8_8_8_8_8_8_8_8_8_8_8

A9:S10:
1_0_1_0_1_0_1_0_1_0_1_0_1_0_1_0_1_0_9
0_1_0_1_0_1_0_1_0_1_0_1_0_1_0_1_0_1_9


A4:A7:
1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1
2_2_2_2_2_2_2_2_2_2_2_2_2_2_2_2_2_2
#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#
#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#

A9:S10:
3_2_1_0_3_2_1_0_1_0_1_0_3_2_1_0_1_0_21
0_1_2_3_0_1_2_3_0_1_0_1_0_1_2_3_0_1_21


A4:A7:
1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1_1
#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#
#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#
#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#_#

A9:S10:
1_0_1_0_1_0_1_0_1_0_1_0_1_0_1_0_1_0_9
0_1_0_1_0_1_0_1_0_1_0_1_0_1_0_1_0_1_9


Are these the results you want given any combination of merged cells
separately by columns and within rows 1-3 and 4-7? If not, **YOU**
need to provide **FULL DETAILS** of what you want, i.e., you need to
show the A9:S10 values you expected in EACH & EVERY scenario above.

In using the check calculation utility in office 2007, it gave the following
response.

To what? You're omitting details again. Others may be able to, but I
can't read your mind nor have any inclination to try.

(10 false's, 1 true, 25 false's)*(41-n/a's)
I expected to find only one true in the first portion. I did not expect to
find the n/a values. There is suppose to be a single value which will return
a -2.76, that matched the location of the one true.

So the SUMPRODUCT formula returned #N/A ?

What were your EXACT contents of A1:Z3 and A4:Z7 including a COMPLETE
list of all blocks of cells that were merged?

I'll return to one of my original observations: your SUMIF ranges are
mismatched, so your original formula was fubar. What are you trying to
do? You've failed to provide a clear explanation of what you expect so
far.

 

[SteveDB1]

I'll review this and get back to you.
Thank you.