Count cells between criteria

[QSGuy]

I have been working on a problem that involves counting cells in certain rows between certain rows that contain a specific parameter

I attach a workbook that shows the problem. This workbook is a “resolved” workbook in that it does what I want it to do ie it gets me the data I want in the cells I want: but it is cumbersome in that I have to do manual workings in column M – so I am looking for a work-round to omit columns L and M.

Basically, I am splitting down a long list of items that are grouped into headings in column B – for the purpose of this example, the headings would be fruit, and the items between headings different varieties of that fruit, with a quantity for each variety of fruit in column C.

In column D in the example, I put a “1” for a heading and a “3” for an item under that heading.
In columns F to J, I split the list into different classes (which doesn’t make much sense with fruit, but obviously does in the real spreadsheet). I want to be able to list the fruit headings according to class and the total amount in each fruit type by summing the quantities of each variety and then total up the amount of each class using the quantities of each fruit – all as shown in columns Q-Z.

Typically, in the actual workbook, I have between 5 and 700 lines of data in each worksheet (and about 40 worksheets per workbook) but that could go a lot higher maybe up to a couple of thousand lines – but that would be unusual – so I have set the array at 1000 lines. I might have up to about 20 classes on each sheet.

I have managed to solve the problem and get the data I require in the cells it require it by adding columns L and M. I didn’t realise that SUMIF does not work with INDIRECT(ADDRESS, as it does with INDEX (cell N6), so although, by adding the formula in column L at each heading I am able to find the line numbers for column M, I have to manually change the second line number of the SUM formula in column M.

To then get the right info into to quants columns of results columns (Q_Z) I also have to add a 1 on the heading line in columns F-J – when all I really want in columns F-J is the quantities from column C.

DESIRED OUTCOME:

To omit columns L & M and put the data directly into columns R, T, V, X, and Z – but at the very least, omit column M and make the formula in column L do the work.

Also, not to have to put a 1 in the heading line in columns F-J – which is currently used by the formulas in columns R, T, V, X and Z.

[XLent]

if I've understood...one option:

Please Login or Register  to view this content.

[QSGuy]

Hey XL Kent

Thanks for your response! Clearly, the formula works, as I put it into the test workbook and it worked fine.

But when I put it into the actual worksheet, it brings up a value! error - which I don't understand as the only difference between the two sheets is that the column D from example is column G in the actual worksheet - I changed the Ds to Gs and expected it to work - but for some reason I don't understand, it brings up an error.

I am still learning about these formulas, but I know enough to know that there is no obvious reason why it won't work in the actual spreadsheet! The only thing I can think of is that there is a maximum number of characters that Index/match can look at - as, in the actual workbook, the headings are around 350 characters long in the worksheet I an working on - but can be longer in other cases: would that make a diffference?

[XLent]

The only thing I can think of is that there is a maximum number of characters that Index/match can look at

yes, that will be the cause... some XL functions are limited to 255 chars so, in this instance you could try swapping out the MATCH for slightly longer AGGREGATE

Please Login or Register  to view this content.

note: given above change I capped ranges to 1000 rows, modify as needed but, per your earlier comments, you shouldn't need to make too large.

[QSGuy]

XLent

Yep, that works spot on! Thanks so much!

My apologies re not mentioning the size of the strings - I didn't realise it was important. But on the positive side, I have learnt a new way to use index/match. I did a LEN calc on the original string and cut it to 100 and your first formula worked fine - so the length of string was the thing blocking the first formula in my actual workbook.