case number 1014140890

%3CLINGO-SUB%20id%3D%22lingo-sub-1958521%22%20slang%3D%22en-US%22%3Ecase%20number%201014140890%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958521%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20test%20spreadsheet%20that%20I%20am%20trying%20to%20sum%20a%20column%20of%20values%20if%20the%20corresponding%20date%20is%20of%20a%20certain%20year%3C%2FP%3E%3CP%3EA3%3AA6%20is%20a%20column%20of%20dates%3C%2FP%3E%3CP%3EB3%3AB6%20is%20column%20of%20values%20I%20want%20to%20sum%3C%2FP%3E%3CP%3EF2%20is%202019..This%20is%20the%20year%20that%20I%20am%20trying%20to%20filter%20by%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1958521%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-1958605%22%20slang%3D%22en-US%22%3ERe%3A%20case%20number%201014140890%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958605%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891762%22%20target%3D%22_blank%22%3E%40drcarnine%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22devyadav2008_0-1607151769028.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237961iA06D39BD5389B995%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22devyadav2008_0-1607151769028.png%22%20alt%3D%22devyadav2008_0-1607151769028.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1958607%22%20slang%3D%22en-US%22%3ERe%3A%20case%20number%201014140890%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958607%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891762%22%20target%3D%22_blank%22%3E%40drcarnine%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESUMPRODUCT%3C%2FSTRONG%3E%20would%20return%20your%20expected%20result%2C%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT((YEAR(A3%3AA6)%3DF2)*B3%3AB6)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1958743%22%20slang%3D%22en-US%22%3ERe%3A%20case%20number%201014140890%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958743%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F567527%22%20target%3D%22_blank%22%3E%40devyadav2008%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help.%20Is%20there%20a%20way%20I%20can%20also%20filter%20by%20C3%3AC6%3F%20I%20have%20been%20trying%20to%20use%20the%20sumifs%20function.%20See%20E3.%20I%20can%20get%20it%20to%20work%20for%20the%20month%20but%20not%20for%20the%20year.%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1958803%22%20slang%3D%22en-US%22%3ERe%3A%20case%20number%201014140890%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958803%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891762%22%20target%3D%22_blank%22%3E%40drcarnine%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20869px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237987iA856C775C40E2DDB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a test spreadsheet that I am trying to sum a column of values if the corresponding date is of a certain year

A3:A6 is a column of dates

B3:B6 is column of values I want to sum

F2 is 2019..This is the year that I am trying to filter by

4 Replies

@drcarnine 

 

devyadav2008_0-1607151769028.png

 

@drcarnine 

SUMPRODUCT would return your expected result, like this:

=SUMPRODUCT((YEAR(A3:A6)=F2)*B3:B6)

@devyadav2008 

Thank you for your help. Is there a way I can also filter by C3:C6? I have been trying to use the sumifs function. See E3. I can get it to work for the month but not for the year. 

Thank you again

@drcarnine 

As variant

image.png