Sumif with named range of nonadjacent cells.

[Keith]

Greetings:

I'm trying to conditionally sum nonadjacent cells.
The condition cells are in a range named "condition" containing (A1,
A3, A5, A7, A9).
The sum cells are in a range named "values" containing (B1, B3, B5,
B7, B9).
The condition is ">0".
The formula is =SUMIF(condition, ">0", values)
The result is #VALUE

(The actual cell references are much further apart, with lots of stuff
in between, so I can't just select a contiguous range. The ranges
listed above are from my small test version of the formula.)

I have verified that each named range has the same number of cells. If
I select A1:A9 and B1:B9 instead of the named ranges, everything works
fine. I've tried this is a standard formula and an array formula with
the same results.

TIA
Keith

 

[T. Valko]

The actual cell references are much further apart

It's better if you tell us the *real* cell refs rather than making up
theoretical samples.

 

[Keith]

It's not a theoretical sample. The references I gave you are the
actual references of a test version I set up to replace this mess:

=(SUMIF(I30,">0",E30))+((SUMIF(I51,">0",E51)))+(((SUMIF
(I72,">0",E72))))+((((SUMIF(I93,">0",E93)))))+(((((SUMIF
(I115,">0",E115))))))+(((((SUMIF(I137,">0",E137))))

in a spreadsheet I'm trying to fix up for someone. My test version was
a proof of concept spreadsheet, but the references and results are
real, not just something I made up in my head.

K

 

[T. Valko]

The references I gave you are the actual references

of a test version I set up to replace this mess

The keyword here is "test version". The test version doesn't translate to
the real version because this type of formula depends solely on what cells
the data is in.

The formula for a range covering every other cell (the test version) won't
translate to one where the range covers every 10th cell or 21st cell.

So, using this as the real reference:

=(SUMIF(I30,">0",E30))+((SUMIF(I51,">0",E51)))+(((SUMIF
(I72,">0",E72))))+((((SUMIF(I93,">0",E93)))))+(((((SUMIF
(I115,">0",E115))))))+(((((SUMIF(I137,">0",E137))))

Well, there's a problem in that the interval is not constant.

I30 to I51 = 22 rows
I51 to I72 = 22 rows
I72 to I93 = 22 rows
I93 to I115 = 23 rows
I115 to I137 = 23 rows

So, you'd be better off using this:

=SUMIF(I30,">0",E30)+SUMIF(I51,">0",E51)+SUMIF(I72,">0",E72)+SUMIF(I93,">0",E93)+SUMIF(I115,">0",E115)+SUMIF(I137,">0",E137)

--
Biff
Microsoft Excel MVP


It's not a theoretical sample. The references I gave you are the
actual references of a test version I set up to replace this mess:

=(SUMIF(I30,">0",E30))+((SUMIF(I51,">0",E51)))+(((SUMIF
(I72,">0",E72))))+((((SUMIF(I93,">0",E93)))))+(((((SUMIF
(I115,">0",E115))))))+(((((SUMIF(I137,">0",E137))))

in a spreadsheet I'm trying to fix up for someone. My test version was
a proof of concept spreadsheet, but the references and results are
real, not just something I made up in my head.

K

 

[Manolo]

"Keith" <[email protected]> escribió en el mensaje
It's not a theoretical sample. The references I gave you are the
actual references of a test version I set up to replace this mess:

=(SUMIF(I30,">0",E30))+((SUMIF(I51,">0",E51)))+(((SUMIF
(I72,">0",E72))))+((((SUMIF(I93,">0",E93)))))+(((((SUMIF
(I115,">0",E115))))))+(((((SUMIF(I137,">0",E137))))

in a spreadsheet I'm trying to fix up for someone. My test version was
a proof of concept spreadsheet, but the references and results are
real, not just something I made up in my head.

K

It's better if you tell us the *real* cell refs rather than making up
theoretical samples.

Hi Keith:

Maybe you can try something shorter like:

=E30*(I30>0)+E51*(I51>0)+E72*(I72>0)+E93*(I93>0)+E115*(I115>0)+E137*(I137>0)

Manolo

 

[Don Guillett]

How about a nice macro
Sub sumifnoncontignamedrnage()
For Each c In Range("Condition")
If c > 0 Then ms = ms + c.Offset(, 1)
Next c
MsgBox ms
End Sub

 

[Keith]

Sounds like you can use named range arguments in a SUMIF if the named
ranges refer to nonadjacent cell.
I guess I'll try multiplication method. Thanks to all who replied.

K