Positioning Subtotals to the next column on right

1cyril1

Board Regular
Hi, I've searched through 4 pages of "Subtotal" posts in this forum but didn't see one that covers what I need. I just want to have Subtotal values appear to the right of the column being sub-totalled rather than directly below each group of numbers. The col to the right would be created just to hold the subtotal values and facilitate filtering on the subtotal lines only. Is this easy to do?

(I've seen somewhat convoluted suggestions elsewhere that involve showing visible cells but I gave up - hoping there's a simpler approach!)

All suggestions most welcome - thanks!

Cyril
 

1cyril1

Board Regular
Many thanks JoeMo, I'm open to all suggestions! The link you posted doesn't seem to be the correct one?

I basically want to subtotal by value (Col-R below) whenever there's a change in PO No. (Col-F) and have it appear in a new Col-S instead of under the sub-total in Col-R. I'm using Win10 and Office 2016. Unfortunately, our security masters in the workplace won't allow me to install any software so this is the best I can do to provide an idea of what my data looks like (some cols hidden or excluded from this extract - it has many cols!)

COL-F
COL-G
COL-J
COL-K
COL-M
COL-P
COL-R
PO Number
Created by
Material group
PO Description
Contract Number
PO Local Currency
PO Value (Euro)
3500120173
xx
IT_T-OPS
Description-1
4600012081
EUR
4,447
3500120194
xx1
VEM_EPART
Description-2
4600015833
EUR
3,383
3500120198
xx2
CCE_SS-BL
Description-3A
4600018480
EUR
7,030
3500120199
xx3
CCE_SS-BL
Description-3B
4600018480
EUR
4,500
3500120202
xx4
FAC_SCAPE
Description-4
4600012534
EUR
3,960
3500120210
xx5
IT_T-OPS
Description-5
4600018652
EUR
6,230

<tbody>
</tbody>

Hope that helps - thanks again for taking the time to understand and provide a solution!
 

JoeMo

MrExcel MVP
Can you clarify for me?
1. Is the data always sorted by PO Number?
2. For the data you posted, there's a change in PO at every line. Do you want a subtotal in col S for each line?
 

1cyril1

Board Regular
Yes, I meant to say the Sort is by PO No. (ascending).

Sorry - in reality there will be loads of duplicate POs but the rushed sample I posted was from a filtered view where duplicates are hidden - there can be between 2 and 50 duplicates (or none!) and the subtotal would sum up all corresponding values to give the total for each PO 'family'. Ideally, I'd like the subtotal value to appear in Col S ..... on same row as each 'bottom' PO with no subtotal row at all but if that complicates things unduly please don't worry about it.

Many thanks once more(y)
 

JoeMo

MrExcel MVP
Looks like this doesn't require VBA. Here's the sample data you posted. Copy the array formula down to cover all your data, and don't forget that array formulas must be confirmed by ctrl+shift+enter not just enter.
Excel Workbook
FGHIJKLMNOPQRS
1PO NumberCreated byMaterial groupPO DescriptionContract NumberPO Local CurrencyPO Value (Euro)Subtot by PO#
23500120173xxIT_T-OPSDescription-14600012081EUR4,447
33500120194xx1VEM_EPARTDescription-24600015833EUR3,3833,383
43500120198xx2CCE_SS-BLDescription-3A4600018480EUR7,0307,030
53500120199xx3CCE_SS-BLDescription-3B4600018480EUR4,5004,500
63500120202xx4FAC_SCAPEDescription-44600012534EUR3,9603,960
73500120210xx5IT_T-OPSDescription-54600018652EUR6,2306,230
Sheet10
 

1cyril1

Board Regular
Excellent Joe - I'd never heard of array formulas before so the learning goes on! Because of the need for care I'd appreciate your expert guidance on the following, before I test it over the next day or two:

1. unlike my sample, the data in my real-life spreadsheet starts in row#7 so 1st formula needed in S7 rather than S2. I could attempt to adjust but not sure I'd get right:oops:
2. each time I need to use the formula (monthly reporting task) the number of rows with data will vary (i.e. do I need to reference the full column to catch all?)

What will the formula need to look like to take account of these 2 factors?

Hopefully, that's all the questions from me - can't wait to try out!
 

JoeMo

MrExcel MVP
Excellent Joe - I'd never heard of array formulas before so the learning goes on! Because of the need for care I'd appreciate your expert guidance on the following, before I test it over the next day or two:

1. unlike my sample, the data in my real-life spreadsheet starts in row#7 so 1st formula needed in S7 rather than S2. I could attempt to adjust but not sure I'd get right:oops:
2. each time I need to use the formula (monthly reporting task) the number of rows with data will vary (i.e. do I need to reference the full column to catch all?)

What will the formula need to look like to take account of these 2 factors?

Hopefully, that's all the questions from me - can't wait to try out!
Below is the formula for S7. Copy it from your browser then enter it in S7, with S7 selected click in the formula bar and press ctrl+shift+enter to convert the formula to an array formula. If you are successful with these steps you will notice that Excel adds opening and closing braces: {=your formula} around the formula signifying it's an array formula.

Array formulas generally take more time to calculate then non-array formulas so it's best not to reference entire columns. The formula will return "" (a zero-length string, which appears like a blank cell to the eye) for any row that has no PO# in col F. This allows you to copy the formula down as far as you think your data will go in the future, while not cluttering the, as yet, unused rows with error values. In the formula below I have assumed that the data will never go beyond row 1000 (see addresses in red font)- change that to whatever you wish.

Rich (BB code):
=IF($F7="","",IF($F7<>$F8,SUM(IF($F$7:$F$1000=$F7,$R$7:$R$1000,0)),""))
 

Peter_SSs

MrExcel MVP, Moderator
Here are two non-array alternatives. They would simply need to be copied down to the end of the data or as far as you think you might ever need without having to consider what the last row of the range might be within the formula itself.

=IF(F7="","",IF(F7=F8,"",SUM(R$7:R7)-SUM(S$6:S6)))
or
=IF(F7="","",IF(F7=F8,"",SUMIF(F$7:F7,F7,R$7:R7)))
 
Last edited:

1cyril1

Board Regular
Sincere thanks Joe and Peter; no excuses now. I'll revert asap and confirm the outcome. I expect this will be of benefit to many Excel users - such a great forum:)
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top