Subtotal with sumproduct and quartile

**tdcsi** · 11-19-2011, 12:36 AM

I've seen several examples of nesting subtotal in a sumproduct calculation and have played around for a few days, but do not know the lingo and concepts well enough to proceed any further. With a lot of help, I have been able to work through all but these two problems. Any suggestions?

=quartile(E23:E1000,1)

and

=sumproduct((B23:B1000<>"")/Countif(B23:B1000,B23:B1000&""))

**darkyam** · 11-19-2011, 02:08 AM

A dummy workbook would really help, especially with the first one, since your syntax seems right. I'm honestly not sure what you're trying to do with the second formula. Maybe I'm too tired, but it reads like you're trying to count the non-blank cells in that range and divide by the most common occurrence in the range.

**daddylonglegs** · 11-19-2011, 03:22 AM

The second formula gives a count of different items in the range specified.

Do you need to combine the two formulas, please explain further.

**darkyam** · 11-19-2011, 08:44 AM

DLL, when I tried the second half of the second formula with a ten cell range, the first four being different and the last six being blank, it returns an array of {1;1;1;1;6;6;6;6;6;6}, which it read as 1. When I changed one of the first four items to be the same as another of them, it returned 2. I put this same value in one of the blanks and it returned 3. I don't understand how it counts different items based on my results. I trust that you're right, I just don't get what I'm missing.

**daddylonglegs** · 11-19-2011, 09:03 AM

In conjunction with the first half you get the "countdiff" count because the first test gives you

{1;1;1;1;0;0;0;0;0;0}

so

{1;1;1;1;0;0;0;0;0;0}/{1;1;1;1;6;6;6;6;6;6}

divides each element of the first array by the same position in array 2 so you get this as the result

{1;1;1;1;0;0;0;0;0;0}

and SUMPRODUCT sums that array to get 4

It's perhaps more understandable when you repeat values. If you put "x" in the first 3 cells and "y" in the next 2 then leave the rest blank then you'd get

{1;1;1;1;1;0;0;0;0;0}/{3;3;3;2;2;0;0;0;0;0}

which equals

{1/3;1/3;1/3;1/2;1/2;0;0;0;0;0}

which sums to 2

It's possible to use just

=sumproduct(1/Countif(B23:B1000,B23:B1000&""))

but that counts blank as a distinct value so with that last example that would give you 3

**tdcsi** · 11-19-2011, 01:19 PM

By all means, any alternative/better solutions to what I have "guessed" at will be greatly appreciated. I will attempt to attach my base template.

The intent of the workbook is to help trend discrepancies in manufacturing based on tank, product size, and personnel. All of the product to product information is entered into a table and a series of calculations helps me trend batch to batch variances to better aid with process improvement. I was just introduced to subtotal a few days ago while I was looking for a means of having the calculations adjust based on the conditions I sort for in the table. Unfortunately, I have a few sumproduct and quartile calculations that I don't know how to incorporate into this. To add to the fun of it all, someone else wrote the sumproduct formula's after I asked for help and I haven't quit figured out how they work, but they seem to.

There are two types of sumproduct calculations. one is to show if a result falls within set parameters (this was not originally posted) and a second one is designed to count different conditions within the same column (this is what the example is). The intent of the second type is to show how many different sizes have been made per condition selected. Several lines of data do have blanks as the original information is lost thus I don't want that info counted.

As you can imagine, I use the quadrille information to show how the average falls in line with the rest of the data set.

Hopefully the description helped more then confused you.

Template.xlsm