SumIf on a non-continuous range

[LTunnicliffe]

How do I do a SumIf on B1, D2, E4, F7 where I want all the non-zero values?

 

[Dave Patton]

try =SUM(rV)

rV is a named range that includes your values.

select the first cell, then press Ctrl and select the other cells
type a name of your choice or rV in the name box

 

[LTunnicliffe]

...without using named ranges. While this will work I want to conditionally sum (actually average, but I can do that) a matrix of values that is 15 X 12, that's alot of named ranges. :x Thanks

 

[NateO]

Don't you want to conditionally count to derive a denominator?

Select all of your non-contiguous cells, name them type in the name box MyRange, (one named range) then try the following:

http://www.mrexcel.com/board2/viewtopic.php?t=40862&start=4

 

[Ekim]

Loren,

There has to be a better way of doing this, but the following works:

=SUMIF(B1,">0",B1)+SUMIF(D2,">0",D2)+SUMIF(E4,">0",E4)+SUMIF(F7,">0",F7)

Regards,

Mike

 

[Juan Pablo González]

The following post shows how you can use COUNTIF with multiple ranges, applies too to SUMIF:

http://www.mrexcel.com/board2/viewtopic.php?p=136110#136110

 

[NateO]

Whoah, sumif <> 0 makes 0 sense. 0+0+0+0+0+5=5.

What are we really after here?

 

[Juan Pablo González]

Also, this

=SUM(B1, D2, E4, F7)

would sum all the non-zero values...

 

[Ekim]

Also, this

=SUM(B1, D2, E4, F7)

would sum all the non-zero values...

Juan,

I knew there had to be a better way!

Regards,

Mike

 

[LTunnicliffe]

Sorry about the confusion... How do you sum non-zero non-continuous? That was a pretty stupid question. I guess I should have used countif, sorry. I was trying to simply the problem.

Well... that still leaves the problem unsolved, I don't want to use Named Ranges due to the magnitude of the problem (12X15). Anything else?