1

This might be an easy question to some but I am completely lost so I would really appreciate any help out there!

        A      
  1    10
  2    20
  3    30
  4    40
  5    50
  6    60
  7    70
  8    80
  9    90
 10   100

Of course my actual work spreadsheet is a lot more complicated than the chart above but the logic I am trying to figure out is this (using array formula):

=SUM(IF(LEN(A1:A3,A8:A10)>0,1)) Control+Shift+Enter I can an error--- I want to write a cell range/reference that completely ignores A4:A7 (40, 50, 60, 70). (This comma is not working when it comes to combining 2 cell ranges that are split.)

Can someone please help me write a cell range/reference that skips the mid-section of the chart! I feel like using OFFSET or INDEX can somehow accommodate the need but I am so lost.

Improve this question
4
  • @Clif Not sure what you mean by "the format of the data" but basically I want the formula above to ignore A4:A7 completely. Right now I don't know how to write the cell range that will go from A1:A10 but remove A4:A7 in between. The comma doesn't work in this case so... would you happen to know a way?
    – EA1234
    Commented Jun 18, 2015 at 2:41
  • @Clif Oh I see yea those asterisks are there just for viewing convenience. I should remove them to mitigate any confusion. Thanks.
    – EA1234
    Commented Jun 18, 2015 at 2:48
  • 1
    This is not an answer as it is not an array formula, and I haven't come up with one, but it may give you some ideas about how to go about this. Paste =IF(AND(MOD(ROW(),7)>=1,MOD(ROW(),7)<=3),INDIRECT("A"&ROW()),"") in B1 and copy down to B10. The sum of column B is now what you want, although I realize it is not the way you wanted to get it. Perhaps there will be someone that will see this and know how to incorporate it into an array formula.
    – Clif
    Commented Jun 18, 2015 at 3:23
  • @Clif Thanks for the idea - I'll definitely incorporate your suggestion. Really appreciate it!
    – EA1234
    Commented Jun 18, 2015 at 3:29

2 Answers 2

Reset to default
1

Eureka! Here is the formula:=SUMPRODUCT(--(MOD(ROW(A1:A10),7)>=1)*--(MOD(ROW(A1:A10),7)<=3)*A1:A10) The rational goes along with what you saw when you pasted the formula in my comment to the range B1:B10. Since this is a SUMPRODUCT function you can just press ENTER.

Improve this answer
2
  • Clif, your formula worked thank you so much! If I may ask a similar question, do you know a way to simply connect 2 non-contiguous ranges? E.g. Connect A1:A3 and A8:A10 (skipping A4:A7)??
    – EA1234
    Commented Jun 18, 2015 at 22:40
  • You are welcome and thanks for the acceptance. I don't think that there is a simple way that will work with non-contiguous ranges, which is kind of reinforced by the number of people who looked at your post. I think that if there was a simple way someone would have given that answer by now. However someone may yet educate both of us. Have a good day.
    – Clif
    Commented Jun 18, 2015 at 22:58
0

Some functions will allow you to use non-contiguous ranges, but typically not array formulas unless you use another criteria (like row number) to explicitly exclude some rows.

With the formula you show in the question, i.e.

=SUM(IF(LEN(A1:A3,A8:A10)>0,1))

That, presumably, is designed to count non-blank cells in the specified ranges - if that's all you want to do then perhaps use COUNTA - that will count non-blank cells......and you can apply it to non-contiguous ranges, e.g.

=COUNTA(A1:A3,A8:A10)

Note that COUNTA will also count cells which are populated with a "formula blank".

If that doesn't do what you want then please explain more fully the calculation you want to do

Improve this answer
1
  • Barry, the question and the example formula was ultimately used to ask for this question: Is there a way to simply connect 2 non-contiguous ranges? E.g. Connect A1:A3 and A8:A10 (skipping A4:A7)?? Sorry if I caused you any confusion but would you happen to know a way?
    – EA1234
    Commented Jun 18, 2015 at 22:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .