array with "separated" data column

[neeshu]

I'm trying to create an array that sums a column of numbers based on a text criteria in the other column. However, my column of data is not all consecutive.

A few questions:

1. Can you select a range of cells then a gap and then another range of cells to use as your array data as long as they are the same?
2. Can some of the cells in the column selected have formulas in them? Or must the range be only data?

I have tried it both of the above ways and it doesn't work. If I only select the first range of data it works, but not when I have a gap and then select the second range of data.

What I have is a column of data by day with a subtotal along the bottom for each day. I think the subtotal is causing the problems, but when I exclude that it still doesn't work.

If I cannot split the column data, is there another way I could do this?

[JBeaucaire]

No sample workbook? No example formulas you've been trying so we can see what you're talking about?

You could use a SUMIF() for each distinct "rang" of cells and add them together.

=SUMIF(A1:A10, "cat", B1:B10) + SUMIF(A45:A55, "cat", B45:B5)


With an example workbook, we might be able to spot something consistent about the data in various ranges and suggest a formula that will "skip" for you.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.