Formula building

%3CLINGO-SUB%20id%3D%22lingo-sub-1953472%22%20slang%3D%22en-US%22%3EFormula%20building%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1953472%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI'm%20trying%20to%20build%20a%20formula%20which%20retrieves%20data%20from%20one%20worksheet%20to%20display%20on%20another%3C%2FP%3E%3CP%3EHere%20is%20what%20I%20am%20trying%20to%20do%3A%3C%2FP%3E%3CP%3Ethe%20formula%20will%20be%20displayed%20in%20cell%20c24%20on%20worksheet%20%22comparison%22%3C%2FP%3E%3CP%3Eon%20worksheet%20%22summary%22%2C%20find%20a%20specific%20value%20(in%20this%20case%2021405%20is%20the%20value)%20in%20table%2035%20(table%2035%20is%20located%20on%20the%20%22summary%22%20worksheet)%20%2C%20in%20column%20C%20(or%20the%20header%20is%20Unit%20on%20the%20table)%20and%20add%20all%20the%20amounts%20associated%20with%20that%20value%20from%20column%20P%20(or%20the%20header%20is%20YTD%20Total%20on%20the%20table)%3C%2FP%3E%3CP%3EIf%20there%20are%20no%20instances%20of%20the%20specific%20value%20then%20return%20a%20n%2Fa%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20also%20be%20using%20this%20formula%20to%20retrieve%20totals%20by%20month%2C%20which%20are%20also%20listed%20in%20table%2035%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20a%20few%20different%20scenarios%20but%20get%20ref%23%20so%20clearly%20I'm%20missing%20something%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELorie%403345%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1953472%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-1953560%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20building%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1953560%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%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20provide%20small%20sample%20file%2C%20it's%20hard%20to%20reproduce%20this%20scenario%20based%20on%20description%20only.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello

I'm trying to build a formula which retrieves data from one worksheet to display on another

Here is what I am trying to do:

the formula will be displayed in cell c24 on worksheet "comparison"

on worksheet "summary", find a specific value (in this case 21405 is the value) in table 35 (table 35 is located on the "summary" worksheet) , in column C (or the header is Unit on the table) and add all the amounts associated with that value from column P (or the header is YTD Total on the table)

If there are no instances of the specific value then return a n/a

 

I will also be using this formula to retrieve totals by month, which are also listed in table 35

 

I've tried a few different scenarios but get ref# so clearly I'm missing something

 

Any suggestions?

 

Lorie@3345

 

3 Replies

@lorie3345 

Perhaps you may provide small sample file, it's hard to reproduce this scenario based on description only.

@Sergei Baklan here is sample 

 

I am currently working on the 2020 comparison YTD Totals , then I will apply the formula to the 2019 figures once it is calculating properly.

 This is what I have started with:

 

=COUNTIF(Table25,MATCH($A24,Table37)) 

 

not sure if that is correct but now I need the formula to continue on to add all instances of the value specified in cell A24 on Table 37 from column P on table 25 (which is YTD Totals for the header). I am trying to get a year over year comparison of how many ltrs each unit is using.

 

Thank you for any help you provide

@lorie3345 

@lorie3345 

Perhaps you mean SUM(), not COUNT(). When

image.png

with

=SUMIF(Table25[UNIT],[@UNIT], Table25[YTD TOTAL])