# Thread: Subtotal Question

1. ## Subtotal Question

XL 97
I am trying to create an easy way to determine a Order to Quote ratio from the data below.

Cust Quote # Order
ABC 123 yes
ABC 456 no
EFG 963 yes
EFG 875 yes
LMN 789 yes
XYZ 147 no

I am trying to get the info in the followoing form:

Cust ID Tot Qoutes Tot Orders Ratio
ABC 2 1 50%
EFG 2 2 100%
LMN 1 1 100%
XYZ 1 1 0%

I can get to this format but it takes a lot of manual work. I tried using SUBTOTAL and calculated field in Pivot Table but I can't get it to produce the data. Is there a way to do this with other built-in functions?

Thanks for any help,
Roger

2. ## Re: Subtotal Question

Sorry, I can't really tell what it is you want to do here. The way it prints out on the board everything runs together and I can't make a connection between were you starting from and the result you want. Could you be a little clearer.

3. ## Re: Subtotal Question

Let me try this again, since the first post didn't display correctly.
XL 97
I am trying to create an easy way to determine a Order to Quote ratio from the data below.

Cust---------Quote------# Order
ABC----------123----------yes
ABC----------456----------no
EFG----------963----------yes
EFG----------875----------yes
LMN----------789----------yes
XYZ----------147----------no

I am trying to get the info in the followoing form:

Cust ID------Tot Qoutes------Tot Orders-----Ratio
ABC----------2-----------------1-------------------50%
EFG----------2-----------------2------------------100%
LMN----------1-----------------1------------------100%
XYZ-----------1-----------------1--------------------0%

I can get to this format but it takes a lot of manual work. I tried using SUBTOTAL and calculated field in Pivot Table but I can't get it to produce the data. Is there a way to do this with other built-in functions?

Thanks for any help,
Roger

4. ## Re: Subtotal Question

Hi,

FWIW, you can make the indents show be enclosing them in "pre" tags from the button on the right.

So, from Martin's original post, with the "pre" tags (enclosed by square brackets) it looks like this:
<pre>Cust Quote # Order
ABC 123 yes
ABC 456 no
EFG 963 yes
EFG 875 yes
LMN 789 yes
XYZ 147 no
</pre>

I am trying to get the info in the followoing form:

<pre>Cust ID Tot Qoutes Tot Orders Ratio
ABC 2 1 50%
EFG 2 2 100%
LMN 1 1 100%
XYZ 1 1 0%

</pre>

Pre tags can cause problems if you're copying the text into somewhere else- the give soft returns instead of hard returns. But I don't think that's applicable here.

5. ## Re: Subtotal Question

Hi Roger,
The following code should do what you want - please note though that it will overwrite your existing data but it will add all the quote nos. into one row and order status too.
Give it a try and let me know if you need any explanation/modifications:
Sub removedups()
Dim intRow As Integer, intQuotes As Integer, intOrders As Integer
Dim strQuotes As String, strOrders As String
On Error Resume Next
intRow = 2
Cells(1, 4).Formula = "Total quotes"
Cells(1, 5).Formula = "Total orders"
Cells(1, 6).Formula = "Hit ratio"
Do While Cells(intRow, 1).Formula <> ""
With Cells(intRow, 1)
If UCase\$(.Offset(0, 2).Formula) = "YES" Then intOrders = 1
strQuotes = .Offset(0, 1).Formula
strOrders = .Offset(0, 2).Formula
intQuotes = 1
Do While .Formula = .Offset(1, 0).Formula
intQuotes = intQuotes + 1
If UCase\$(.Offset(1, 2).Formula) = "YES" Then intOrders = intOrders + 1
strQuotes = strQuotes & ", " & .Offset(1, 1).Formula
strOrders = strOrders & ", " & .Offset(1, 2).Formula
.Offset(1, 0).EntireRow.Delete
Loop
.Offset(0, 1).Value = strQuotes
.Offset(0, 2).Value = strOrders
.Offset(0, 3).Value = intQuotes
.Offset(0, 4).Value = intOrders
With .Offset(0, 5)
.Value = intOrders / intQuotes
.NumberFormat = "0.00%"
End With
intRow = intRow + 1
End With
Loop
End Sub

Hope that helps.

6. ## Re: Subtotal Question

alternatively of course, you could add 2 columns - one with just 1s in every cell (to count quotes) and one using =if(C1="Yes",1,0) (assuming the Order Yes/No is in column C) then you can use subtotals by customer and sum these two new columns. You'll need to add the hit ratio formulae manually though.
Hope that helps.

7. ## Re: Subtotal Question

If your list is in columns A - C and you create a unique list of customers (using the advanced data filter) in column E then Total Quotes will be:

{=SUM(N((\$A\$2:\$A\$100)=E2))}

and
Total orders will be:

{=SUM(N((\$A\$2:\$A\$100)=E2)*N((\$C\$2:\$C\$100)="yes"))}

These are array functions and must be entered using Control + Shift + Enter

If this is unclear let me know and I will send you a small spreadsheet example.

8. ## Re: Subtotal Question

You can use a Pivot Table:
Select the data including the column headings. I copied your data to C10:E16 and renamed # to Num
choose Data PivotTableReport
Follow the wizard. Since the range is highlighted you don't need to change that. When you get to the bit where you drag the fields onto the table:
drag CustomerQuote from the list on the right onto the part of the table marked Row
drag Order onto Column
drag Num into the body of the table where it says Data. Then double-click on the Num in the Data section and choose Summarize by Count from the list. It will then say Count of Num.

Finish the Wizard. I usually put the table into a new sheet, but in this case I put it at G10.
You may need to play with default settings. My table comes out (G10:J16)
Count of Num Order
Cust Quote no yes Grand Total
ABC 1 1 2
EFG 0 2 2
LMN 0 1 1
XYZ 1 0 1
Grand Total 2 4 6
(I can't get the tabs right either, but I hope it makes sense)

Then in the column beside the
Grand Total, you can put the formula =I12/J12 in cell K12 (for my example), format as percent and copy down.
Does this help?
Ruth Callcott
[img]/w3timages/icons/dragon.gif[/img][img]/w3timages/icons/spook.gif[/img]

9. ## Re: Subtotal Question

... or you can use my inelegant but simple approach attached. (Occam's razor.)

10. ## Re: Subtotal Question

Thanks everyone for your help. All of your ideas worked and I greatly appreciate the help. It's fantastic to have so many people that are willing to help.[img]/w3timages/icons/smile.gif[/img]

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•