SOLVED

Sum data based on selected criteria

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3135357%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ESum%20data%20based%20on%20selected%20criteria%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3135357%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHi%20Excel%20Experts!%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%E2%80%99m%20trying%20to%20create%20a%20formula%20to%20sum%20referenced%20and%20filtered%20data%20base%20on%20an%20input%20year.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20have%20data%20being%20pulled%20from%20sheets%20based%20on%20different%20criteria%20and%20want%20to%20be%20able%20to%20QC%20against%20the%20sum%20of%20the%20values%20based%20on%20the%20year%20selected.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%E2%80%99ve%20created%20a%20simplified%20version%20below%3A%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CDIV%20class%3D%22%5C%26quot%3B%5C%26quot%3B%22%3E%26nbsp%3B%26lt%3B%5C%2FDIV%26gt%3B%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F346154i18C67DDDC4D22560%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22image003.png%22%20alt%3D%22%5C%26quot%3Bimage003.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EB6%20is%20a%20dropdown%20option%20to%20select%20years%20in%20B1%3AQ1%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20QC%20the%20number%20in%20L9%20by%20looking%20up%20the%20input%20year%20in%20B6%20and%20summing%20the%20data%20in%20the%20top%20table%20%E2%80%93%20i.e.%20in%20this%20case%2C%20from%202009%20would%20be%20SUM(F2%3AN2)%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI've%20tried%20a%20combination%20of%20IF%20and%20SUM%20and%20FILTER%20but%20need%20some%20expert%20advice!%20Thanks%20in%20advance%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3135357%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EOffice%20365%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional Contributor

Hi Excel Experts!

 

I’m trying to create a formula to sum referenced and filtered data base on an input year.

 

I have data being pulled from sheets based on different criteria and want to be able to QC against the sum of the values based on the year selected.

 

I’ve created a simplified version below:

 

image003.png

 

B6 is a dropdown option to select years in B1:Q1

 

I want to be able to QC the number in L9 by looking up the input year in B6 and summing the data in the top table – i.e. in this case, from 2009 would be SUM(F2:N2)

 

I've tried a combination of IF and SUM and FILTER but need some expert advice! Thanks in advance

5 Replies

@pawswag What does "to QC a number" mean? And what's the logic for only summing 2009 through 2017?

QC - quality check
I will be selecting a year as a look forward date (in the example 2009) and want to sum values from this date forward.

@pawswag 

Why from 2009 to 2017, not from 2009 to 2020 or 2015?

only because in the example i created the dates went up to 2017!

in the real data I want to be able to sum from a look forward date (in the past) to a future date
best response confirmed by pawswag (Occasional Contributor)
Solution

@pawswag Still don't understand what "quality checking a number" means, but perhaps the attached file contains the type of solution you are asking for. Screenshot 2022-02-09 at 06.34.36.png