SOLVED

SUMIF for first values only

%3CLINGO-SUB%20id%3D%22lingo-sub-1639110%22%20slang%3D%22en-US%22%3ESUMIF%20for%20first%20values%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639110%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20like%20some%20help%20pls%20..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20setup%20as%20follows%3A%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%22arumel_0-1599390741716.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216813iD62E82749FFC7EB3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22arumel_0-1599390741716.png%22%20alt%3D%22arumel_0-1599390741716.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIn%20column%20E%20i%20would%20like%20to%20show%20the%20budget%20once%20for%20each%20GL%20per%20month%20..%20so%20in%20Jan%20i%20only%20want%20to%20see%2058%2C937%20as%20the%20budget%20for%20GL%2057010104..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHelp%20much%20appreciated%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1639110%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-1639156%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20for%20first%20values%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639156%22%20slang%3D%22en-US%22%3EIn%20cell%20E9%20you%20could%20try%20%3DIF(%20AND(%20A9%3DA8%2C%20B9%3DB8)%2C%20D8%2C%200%20)%20then%20copy%20that%20formula%20to%20the%20other%20cells%20in%20that%20column%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1639177%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20for%20first%20values%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639177%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%2C%20but%20it%20doesn't%20seem%20to%20work%20..%20maybe%20i%20am%20explaining%20it%20wrong%20..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eattached%20is%20my%20file%20with%20periods%20Jan%20-Dec%20-%20within%20each%20period%20i%20have%20duplicate%20GL%20account%20lines%20-%20in%20the%20actual%20column%20i%20want%20to%20show%20for%20each%20month%20the%20budget%20value%20for%20the%20GL%20only%20once%20..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20time%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1639236%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20for%20first%20values%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639236%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F783561%22%20target%3D%22_blank%22%3E%40arumel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20your%20sample%20confuses.%20You%20say%20budget%20for%20each%20account%20shall%20be%20shown%20once%20per%20month.%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%20360px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216822i3B2937623D5799BA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20the%20sample%20for%20the%20accounts%20which%20are%20not%20duplicates%20you%20show%20nothing%2C%20for%20repeating%20budget%20you%20ignore%20only%20first%20record%20and%20keep%20all%20the%20rest.%20Which%20exactly%20result%20do%20you%20expect%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1639239%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20for%20first%20values%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639239%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20..%20the%20above%20pic%20is%20the%20result%20from%20the%20formula%20suggested%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%20..(which%20does%20not%20quite%20work%20out)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20column%20E%20to%20show%20only%20the%20first%20value%20of%20the%20budget%20for%20that%20GL%20(for%20that%20month)%20..%20i.e%20GL%2057010107%20on%20row%2012%20should%20show%208%2C333%20in%20column%20E%20....%26nbsp%3B%20E14%20and%20E15%20should%20show%20zero%20as%20the%20GL%20is%20a%20duplicate%20for%20Jan%20..%20similarly%20costs%20relating%20to%20Feb%20should%20show%208%2C333%20in%20E76%20and%20zero%20values%20for%20E77%20and%20E78%20..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20clarifies%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20time%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1639373%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20for%20first%20values%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639373%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F783561%22%20target%3D%22_blank%22%3E%40arumel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20like%20this%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%20380px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216825i543E3354D9AF907A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(COUNTIFS(%24A%241%3A%24A2%2CA2%2C%24B%241%3A%24B2%2CB2)%26gt%3B1%2C0%2CD2)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1639374%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20for%20first%20values%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639374%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%20so%20much%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20..%20that%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20great%20day%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello all, 

 

Would like some help pls ..

 

I have a spreadsheet setup as follows:

 

arumel_0-1599390741716.png

In column E i would like to show the budget once for each GL per month .. so in Jan i only want to see 58,937 as the budget for GL 57010104..

 

Help much appreciated

7 Replies
In cell E9 you could try =IF( AND( A9=A8, B9=B8), D8, 0 ) then copy that formula to the other cells in that column

Thanks @Wyn Hopkins , but it doesn't seem to work .. maybe i am explaining it wrong ..

 

attached is my file with periods Jan -Dec - within each period i have duplicate GL account lines - in the actual column i want to show for each month the budget value for the GL only once ..

 

Thanks for your time

@arumel 

Sorry, your sample confuses. You say budget for each account shall be shown once per month.

image.png

In the sample for the accounts which are not duplicates you show nothing, for repeating budget you ignore only first record and keep all the rest. Which exactly result do you expect?

Hello @Sergei Baklan  .. the above pic is the result from the formula suggested by @Wyn Hopkins  ..(which does not quite work out)

 

I need column E to show only the first value of the budget for that GL (for that month) .. i.e GL 57010107 on row 12 should show 8,333 in column E ....  E14 and E15 should show zero as the GL is a duplicate for Jan .. similarly costs relating to Feb should show 8,333 in E76 and zero values for E77 and E78 ..

 

Hope this clarifies

 

Thanks for your time

best response confirmed by arumel (New Contributor)
Solution

@arumel 

If like this

image.png

formula is

=IF(COUNTIFS($A$1:$A2,A2,$B$1:$B2,B2)>1,0,D2)

thanks so much @Sergei Baklan  .. that work.

 

Have a great day

@arumel , you are welcome, glad to help