SOLVED

Use SUMIFS on Multiple Columns using BYCOL?

%3CLINGO-SUB%20id%3D%22lingo-sub-3405509%22%20slang%3D%22en-US%22%3EUse%20SUMIFS%20on%20Multiple%20Columns%20using%20BYCOL%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3405509%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20a%20BOM%20template%20that%20needs%20to%20accommodate%20multiple%20end%20items.%20On%20the%20Purchased%20items%20break%20down%20we%20need%20to%20list%20all%20purchased%20items%20then%20consolidate%20the%20list%20of%20purchased%20items%20(Rows)%20and%20maintain%20the%20variable%20number%20of%20end%20items%20(Columns)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eoriginal%20table%20of%20purchased%20items%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3ETable1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3Eams%201%20qty%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3Easm%202%20qty%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3Easm%203%20qty%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E...%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EPurchase%20Item%201%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EPurchase%20Item%202%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EPurchase%20Item%203%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EPurchase%20Item%204%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EPurchase%20Item%203%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EPurchase%20Item%202%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EPurchase%20Item%201%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConsolidated%20table%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22105%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3ETable2%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3Eams%201%20qty%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3Easm%202%20qty%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3Easm%203%20qty%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E...%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EPurchase%20Item%201%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EPurchase%20Item%202%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E2%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CP%3EPurchase%20Item%203%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E9%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EPurchase%20Item%204%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20a%20formula%20to%20remove%20duplicates%20in%20the%26nbsp%3BPurchase%20Items%20column%20is%20easy%2C%20that's%20not%20my%20issue.%3C%2FP%3E%3CP%3EI%20can%20use%20a%20SUMIFS%20of%20each%20individual%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUMIFS(INDEX(%5BTable%201%20range%20of%20all%20qty%20values%5D%2C%2C1%2C1)%2CINDEX(%5BTable%201%20Purchase%20Item%20range%5D%2C%2C1)%2C%5BTable%202%20Purchase%20Item%20Range%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20issue%20is%20getting%20the%20SUMIFS%20to%20function%20on%20all%20qty%20columns%20with%20out%20having%20to%20copy%20paste%20the%20formula%20to%20each%20column.%20I%20would%20like%20for%20it%20to%20do%20this%20dynamically.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20using%20a%20BYCOL%20function%20with%20a%20LAMBDA%20containing%20the%20SUMIFS%2C%20but%20that%20fails..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DBYCOL((IF(%5BTable%201%20Asm%20list%20range%5D%26gt%3B0%2C1%2C0)*%5BTable%201%20Asm%20list%20range%5D)%2CLAMBDA(array%2CSUMIFS(INDEX(%5BTable%201%20range%20of%20all%20qty%20values%5D%2C%2Carray%2C1)%2CINDEX(%5BTable%201%20Purchase%20Item%20range%5D%2C%2C1)%2C%5BTable%202%20Purchase%20Item%20Range%5D)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eany%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3405509%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3405632%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20SUMIFS%20on%20Multiple%20Columns%20using%20BYCOL%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3405632%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1395898%22%20target%3D%22_blank%22%3E%40Jagodragon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20it%20happens%2C%20I%20have%20some%20LAMBDA%20functions%20which%20will%20be%20of%20use%20to%20you%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20create%20named%20functions%20for%20these%20two%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERECURSIVEFILTER%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgist.github.com%2Fncalm%2Ff1c7b5bd8fe86528da88798b545053c8%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgist.github.com%2Fncalm%2Ff1c7b5bd8fe86528da88798b545053c8%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGROUPAGGREGATE%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgist.github.com%2Fncalm%2F29c473a940997c3b112bf06e7e71cf15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgist.github.com%2Fncalm%2F29c473a940997c3b112bf06e7e71cf15%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%2C%20you%20define%20a%20control%20array%20which%20contains%20either%20the%20word%20group%2C%20or%20the%20name%20of%20an%20aggregate%20function%20you%20want%20to%20use%2C%20then%20you%20select%20the%20table%20you%20want%20to%20apply%20the%20lambda%20to%2C%20select%20the%20control%20array%2C%20and%20it%20produces%20the%20output.%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%22groupaggregate%20for%20jagodragon.gif%22%20style%3D%22width%3A%20919px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F373614i9199CA12C717A6A0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22groupaggregate%20for%20jagodragon.gif%22%20alt%3D%22groupaggregate%20for%20jagodragon.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EApart%20from%20the%20%22group%22%20fields%20(of%20which%20the%20function%20supported%20nested%20row%20headers)%2C%20these%20aggregates%20are%20supported%20at%20time%20of%20writing%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%7B%22textjoin%22%2C%0A%22sum%22%2C%0A%22min%22%2C%0A%22max%22%2C%0A%22counta%22%2C%0A%22count%22%2C%0A%22average%22%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you'd%20like%20to%20get%20a%20better%20understanding%20of%20how%20it%20works%2C%20you%20are%20welcome%20to%20read%20this%20blog%20post%20which%20explains%20it%20in%20detail%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.flexyourdata.com%2Fblog%2Fquickly-create-summary-tables-in-excel-with-just-one-formula%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.flexyourdata.com%2Fblog%2Fquickly-create-summary-tables-in-excel-with-just-one-formula%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3405691%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20SUMIFS%20on%20Multiple%20Columns%20using%20BYCOL%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3405691%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1395898%22%20target%3D%22_blank%22%3E%40Jagodragon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUMPRODUCT((%24A%242%3A%24A%248%3D%24A11)*(%24B%241%3A%24D%241%3DB%2410)*%24B%242%3A%24D%248)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAn%20alternative%20could%20be%20SUMPRODUCT.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3405872%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20SUMIFS%20on%20Multiple%20Columns%20using%20BYCOL%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3405872%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1395898%22%20target%3D%22_blank%22%3E%40Jagodragon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20to%20allow%20for%20a%20dynamic%20table%20range%2C%20MMULT%20could%20be%20an%20option%2C%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%22lori_m_0-1653054411876.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F373616i4A23D060225C19D2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22lori_m_0-1653054411876.png%22%20alt%3D%22lori_m_0-1653054411876.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-3407035%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20SUMIFS%20on%20Multiple%20Columns%20using%20BYCOL%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3407035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3BThank%20you!%20this%20is%20exactly%20what%20I%20needed!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

We have a BOM template that needs to accommodate multiple end items. On the Purchased items break down we need to list all purchased items then consolidate the list of purchased items (Rows) and maintain the variable number of end items (Columns)

 

original table of purchased items 

Table1ams 1 qtyasm 2 qtyasm 3 qty...
Purchase Item 1112 
Purchase Item 21 1 

Purchase Item 3

25  
Purchase Item 45 1 
Purchase Item 314  
Purchase Item 2311 
Purchase Item 14 1 

 

Consolidated table

Table2ams 1 qtyasm 2 qtyasm 3 qty...
Purchase Item 1513 
Purchase Item 231

2

 

Purchase Item 3

39  
Purchase Item 45 1 

 

Using a formula to remove duplicates in the Purchase Items column is easy, that's not my issue.

I can use a SUMIFS of each individual column.

 

 

=SUMIFS(INDEX([Table 1 range of all qty values],,1,1),INDEX([Table 1 Purchase Item range],,1),[Table 2 Purchase Item Range])

 

 

My issue is getting the SUMIFS to function on all qty columns with out having to copy paste the formula to each column. I would like for it to do this dynamically.

 

I tried using a BYCOL function with a LAMBDA containing the SUMIFS, but that fails..

 

 

 

=BYCOL((IF([Table 1 Asm list range]>0,1,0)*[Table 1 Asm list range]),LAMBDA(array,SUMIFS(INDEX([Table 1 range of all qty values],,array,1),INDEX([Table 1 Purchase Item range],,1),[Table 2 Purchase Item Range])))

 

 

 

any suggestions?

 

4 Replies

@Jagodragon 

 

As it happens, I have some LAMBDA functions which will be of use to you here.

 

Please create named functions for these two:

 

RECURSIVEFILTER:

https://gist.github.com/ncalm/f1c7b5bd8fe86528da88798b545053c8

 

GROUPAGGREGATE:

https://gist.github.com/ncalm/29c473a940997c3b112bf06e7e71cf15

 

As you can see, you define a control array which contains either the word group, or the name of an aggregate function you want to use, then you select the table you want to apply the lambda to, select the control array, and it produces the output.

 

groupaggregate for jagodragon.gif

 

Apart from the "group" fields (of which the function supported nested row headers), these aggregates are supported at time of writing:

 

{"textjoin",
"sum",
"min",
"max",
"counta",
"count",
"average"}

 

If you'd like to get a better understanding of how it works, you are welcome to read this blog post which explains it in detail:

 

https://www.flexyourdata.com/blog/quickly-create-summary-tables-in-excel-with-just-one-formula/

@Jagodragon 

=SUMPRODUCT(($A$2:$A$8=$A11)*($B$1:$D$1=B$10)*$B$2:$D$8)

An alternative could be SUMPRODUCT. 

best response confirmed by Jagodragon (Occasional Contributor)
Solution

@Jagodragon 

Maybe to allow for a dynamic table range, MMULT could be an option,

 

lori_m_0-1653054411876.png

 

@lori_m Thank you! this is exactly what I needed!