Formula and tables

%3CLINGO-SUB%20id%3D%22lingo-sub-3081071%22%20slang%3D%22en-US%22%3EFormula%20and%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3081071%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%3C%2FP%3E%3CP%3ENeeding%20some%20help%3C%2FP%3E%3CP%3EI%20have%20a%20large%20multi-worksheet%20document%20with%20multiple%20tables%20all%20gathering%20info%20from%20each%20other%20through%20different%20formulas%20and%20document%20links%3C%2FP%3E%3CP%3EHere%20is%20what%20I%20am%20trying%20to%20do%3C%2FP%3E%3CP%3EColumn%20A%20in%20table%2051%20has%20a%20list%20of%20asset%20numbers.%20Column%20B%20in%20Table%2051%20needs%20to%20show%20data%20calculated%20from%20column%20P%20in%20Table%203%20IF%20column%20F%20in%20Table%203%20has%20a%20specific%20asset%20number%20listed.%20If%20there%20are%20multiple%20instances%20of%20the%20unit%20number%20listed%20in%20column%20F%20of%20Table%203%2C%20the%20data%20from%20each%20instance%20in%20Column%20P%20of%20Table%203%20needs%20to%20be%20added%20together%20then%20listed%20in%20the%20correct%20corresponding%20asset%20number%20cell%20in%20Table%2051.%3C%2FP%3E%3CP%3EDoes%20that%20make%20sense%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3081071%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3081289%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20and%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3081289%22%20slang%3D%22en-US%22%3EI%20know%2C%20it's%20hard%20to%20explain%3CBR%20%2F%3EI%20tried%20a%20SUMIF%20-%20got%20%23spill%20error%20and%20INDEX%2FMATCH%20-%20got%20%23N%2FA%20error%3CBR%20%2F%3E%3CBR%20%2F%3Ebasically%20have%20a%20list%20of%20assets%20and%20I%20need%20to%20see%20how%20many%20liters%20of%20fuel%20each%20unit%20burns%20on%20a%20month%20to%20month%20basis.%20The%20raw%20data%20comes%20from%20our%20fuel%20vendor%20which%20I%20paste%20into%20a%20%22data%22%20table.%20I%20have%20a%20new%20worksheet%20and%20therefore%20a%20new%20data%20table%20in%20the%20document%20for%20each%20month.%20The%20raw%20data%20lists%20fuel%20cards%20and%20units.%20The%20%22data%22%20table%20I%20create%20is%20then%20used%20in%20a%20new%20table%20to%20break%20out%20how%20many%20liters%20are%20charged%20to%20specific%20fuel%20cards%20and%20what%20unit%20was%20assigned%20to%20each%20fuel%20card%20at%20the%20time%20of%20purchase.%20I%20have%20that%20part%20done.%20Now%20I%20need%20to%20create%20another%20table%20which%20breaks%20out%20the%20total%20liters%20by%20unit%2C%20not%20by%20fuel%20card%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3081166%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20and%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3081166%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F858968%22%20target%3D%22_blank%22%3E%40lorie3345%3C%2FA%3E%26nbsp%3BTo%20be%20honest%2C%20it's%20all%20a%20bit%20vague.%20But%20when%20you%20say%20%22%3CSPAN%3Et%3CEM%3Ehe%20data%20from%20each%20instance%20in%20Column%20P%20of%20Table%203%20needs%20to%20be%20added%20together%3C%2FEM%3E%22%20it%20seems%20you%20are%20looking%20at%20numerical%20values%20in%20%22column%20P.%20Look%20into%20the%20SUMIF%20and%2For%20SUMIFS%20functions.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello All

Needing some help

I have a large multi-worksheet document with multiple tables all gathering info from each other through different formulas and document links

Here is what I am trying to do

Column A in table 51 has a list of asset numbers. Column B in Table 51 needs to show data calculated from column P in Table 3 IF column F in Table 3 has a specific asset number listed. If there are multiple instances of the unit number listed in column F of Table 3, the data from each instance in Column P of Table 3 needs to be added together then listed in the correct corresponding asset number cell in Table 51.

Does that make sense?

7 Replies

@lorie3345 To be honest, it's all a bit vague. But when you say "the data from each instance in Column P of Table 3 needs to be added together" it seems you are looking at numerical values in "column P. Look into the SUMIF and/or SUMIFS functions.

I know, it's hard to explain
I tried a SUMIF - got #spill error and INDEX/MATCH - got #N/A error

basically have a list of assets and I need to see how many liters of fuel each unit burns on a month to month basis. The raw data comes from our fuel vendor which I paste into a "data" table. I have a new worksheet and therefore a new data table in the document for each month. The raw data lists fuel cards and units. The "data" table I create is then used in a new table to break out how many liters are charged to specific fuel cards and what unit was assigned to each fuel card at the time of purchase. I have that part done. Now I need to create another table which breaks out the total liters by unit, not by fuel card
sorry, I forgot to add
sometimes one asset may have two or three fuel cards so all the liters from all three cards would need to be added together and displayed in the total liters cell for that specific asset number in the new table I am trying to build

@lorie3345 A spill error means that you have a recent Excel version. Spilling means that Excel tries to put a result in multiple cells in one go, but it can't as something is in the way. Would have to see your file. Perhaps Power Query would be worth looking into. But again, I can't tell without seeing the file.

I created a modified version of my document (removed all sensitive/confidential data)
Am I able to upload it to you somehow?

@lorie3345 You can send it to me via a direct message. No guarantees though that I'm going to look at it tonight. I'm on CET and it's diner time. Thereafter, ice hockey.

haha, no problem
thank you for having a look when you have some time