This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

I am trying to find a formulaic approach to establishing the adjusted totals in column J on the attached spreadsheet. This is derived by taking the original supplier amount (range B2 to B4) and then adding the adjustments in column G.

The problem I have is that the layout of the table in columns D to G varies in the number of lines of adjustment per supplier and the supplier name is only output in the first line of each adjustment (see cell D2 - 'Smiths Ltd')

Example

Smiths Ltd 'Adjusted Total' = 2000 + 20000 + 1705.47 + 2890.22 = 26595.69

I would like to use a formula that references the 2000 and then automatically picks up the 'adjustments table' and recognizes how many adjustment lines there are.

I thought that the offset formula might work, but couldn't figure it out.

thanks,

David

Experts-Exchange-q1.xlsx

The problem I have is that the layout of the table in columns D to G varies in the number of lines of adjustment per supplier and the supplier name is only output in the first line of each adjustment (see cell D2 - 'Smiths Ltd')

Example

Smiths Ltd 'Adjusted Total' = 2000 + 20000 + 1705.47 + 2890.22 = 26595.69

I would like to use a formula that references the 2000 and then automatically picks up the 'adjustments table' and recognizes how many adjustment lines there are.

I thought that the offset formula might work, but couldn't figure it out.

thanks,

David

Experts-Exchange-q1.xlsx

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

If you repeat the supplier name in Col D

you could use

```
=B2+SUMIF($D$2:$D$8,A2,$G$2:$G$8)
```

RegardsExperts-Exchange-q1V1.xlsx

i used two helper columns to calculate exactly as per your need, without tempering with your original table.

please see attached workbook

=VLOOKUP([@Supplier],Table

EE.xlsx

the(positive) sum of negatives is negative

see example

Experts-Exchange-q1V2.xlsx

the square brackets that you see are the Structured References of Excel Tables. Excel Tables function is available from Excel 2007 onward. i converted your ranges to Excel Tables, because one of the great features of Excel table is its Dynamic range expansion, meaning that if you add more data beneath the existing table then the excel table will automatically encompass the additional added data and you do not need to update your formulas becuase the formulas everything else in the table will automatically get updated by itself.

now, in order to learn in detail what are those breakets, @signs, i suggest you read a very well explained detail about Structured Referencing of Excel table http://peltiertech.com/structured-referencing-excel-tables/

then you will understand what all those strange signs you see the formula. i could have wrriten it here, but i could not explain it better than Zack Barresse & Kevin Jones who are the real Experts on Excel tables, they also have wrriten a Book called Excel tables, if you are inerested in Mastering Excel tables, i suggest you get a copy of that book. it is available in amazon and also in their website.

now back to the formula. i have now attached the version without the excel tables. so you do not see those weird references but the normal cell references

to better understand the formula, i will break it down in details.

we could not have done this without using the helped columns of H and I

lets look first in column H. the formula is countif performed in expandable range meaning that if you see the E1:E2 E$1 is locked witht he dollar sign in front of the $1 so that every-time the cell goes down it will start from the top and expands by each cell this helper column looks considers the number incrementor to reset whenever similar to the first one which is X143 appears again and it goes until it finds another X143 and then puts one. then the second helper column is to put repetitive number incrementor, so that everytime there is X143 in get increases and it goes with the same number until there is another X143 and you can see that in that column.

now we will talk about the formula in L column =VLOOKUP(Sheet5!$K2,Sheet5

the formula is two part, first one is a easy Vlookup basically it looks from the ID in K and returns the number from the B matched with the name. second part is joined with the plus sign + that means to take that amount form the B column and + add it with result of SUMPRODUCT

so sumproduct uses helper column data to do the calculation.

SUMPRODUCT((Sheet5!$I$2:$I

SUMPRODUCT first line is Sheet5!$I$2:$I$8=ROWS($L$2

ROWs fucntion has one arument that when you give the reference of a range it returns the number of rows in that range.

now, in sumproduct it will check column I and whatever cells that have a value corresponding to the Row incrementor number it will result true and falses true means match it found and then it multipies by cells in column (Sheet5!$G$2:$G$8))) so for zeros multuplies by corresponding G column result is zero and for those with 1 it returns the values of column G and then sumproduct basically adds them together and then sumproduct result and vlookup result gets summed at the end.

please see the attached file and feel free to come back if you need more clarification.

without-table-ref.xlsx

on your question from ID: 41381159, please check my latest attachment in post ID: 41379600

it does not have reference to J5.

Happy New Year!

in response to your question post ID: 41381206

the solution which i provided to the original post, is exactly mirroring what the example file you uploaded. now from what i understand from the above post, is that what you are looking for is completely different from the original post.

i suggest you create a comprehensive dummy sample file , exactly as per your real data, including the pivot table you pointed. then indicate the correct amount where you need the formula to calculate. then i would be able to help.

for this new set of requirement, i suggest you open a new question, i will try to come up with solution asap.

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial