Help with sum ifs formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2812404%22%20slang%3D%22en-US%22%3EHelp%20with%20sum%20ifs%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2812404%22%20slang%3D%22en-US%22%3E%3CP%3EI%E2%80%99m%20am%20trying%20to%20workout%20a%20formula%20and%20praying%20someone%20can%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20a%20formula%20which%20can%20identify%20a%20particular%20item%20from%20a%20list%20then%20add%20the%20quantities.%20As%20a%20company%20can%20have%20more%20than%20one%20item%20at%20any%20one%20time%20there%20are%20several%20columns%20where%20the%20item%20can%20be%20found%20with%20a%20matching%20quantity%20column.%20I%20need%20to%20be%20able%20to%20add%20across%20the%20spreadsheet%20how%20many%20apples%2C%20oranges%20etc%20have%20been%20given%20for%20the%20month%20whilst%20keeping%20one%20line%20for%20each%20company.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20hope%20that%20make%20sense.%20I%20can%20do%20a%20sumifs%20formula%20accounting%20for%20the%201st%20instance%20of%20item%20and%20quantity%20but%20not%20the%20others%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2812404%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2812529%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20sum%20ifs%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2812529%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1175424%22%20target%3D%22_blank%22%3E%40Sel_excel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2815193%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20sum%20ifs%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2815193%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1175424%22%20target%3D%22_blank%22%3E%40Sel_excel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(IF((MONTH(%24B%242%3A%24B%244)%3D%24C8)*(%24C%242%3A%24C%244%3DD%247)%3B%24D%242%3A%24D%244)%3BIF((MONTH(%24B%242%3A%24B%244)%3D%24C8)*(%24E%242%3A%24E%244%3DD%247)%3B%24F%242%3A%24F%244)%3BIF((MONTH(%24B%242%3A%24B%244)%3D%24C8)*(%24G%242%3A%24G%244%3DD%247)%3B%24H%242%3A%24H%244)%3BIF((MONTH(%24B%242%3A%24B%244)%3D%24C8)*(%24I%242%3A%24I%244%3DD%247)%3B%24J%242%3A%24J%244))%3C%2FP%3E%3CP%3EI%20suppose%20your%20table%20is%20in%20range%20A1%3AJ4.%3C%2FP%3E%3CP%3EEnter%20list%20of%20all%206%20fruits%20in%20range%20D7%3AI7.%3C%2FP%3E%3CP%3EEnter%20%22Month%22%20in%20cell%20C7%20and%20number%20of%20month%20%229%22%20and%20%2210%22%20in%20cells%20C8%20and%20C9.%3C%2FP%3E%3CP%3EMaybe%20you%20have%20to%20replace%20%22%20%3B%20%22%20with%20%22%20%2C%20%22%20depending%20on%20your%20version%20of%20Excel.%3C%2FP%3E%3CP%3ENow%20you%20can%20enter%20above%20formula%20in%20D8%20as%20matrixformula%20with%20ctrl%2Bshift%2Benter.%3C%2FP%3E%3CP%3EThen%20you%20can%20copy%20formula%20across%20range%20D8%3AI9.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I’m am trying to workout a formula and praying someone can help.

 

I need a formula which can identify a particular item from a list then add the quantities. As a company can have more than one item at any one time there are several columns where the item can be found with a matching quantity column. I need to be able to add across the spreadsheet how many apples, oranges etc have been given for the month whilst keeping one line for each company. 

I hope that make sense. I can do a sumifs formula accounting for the 1st instance of item and quantity but not the others

 

 

3 Replies

@Sel_excel 

See the attached version.

@Sel_excel 

=SUM(IF((MONTH($B$2:$B$4)=$C8)*($C$2:$C$4=D$7);$D$2:$D$4);IF((MONTH($B$2:$B$4)=$C8)*($E$2:$E$4=D$7);$F$2:$F$4);IF((MONTH($B$2:$B$4)=$C8)*($G$2:$G$4=D$7);$H$2:$H$4);IF((MONTH($B$2:$B$4)=$C8)*($I$2:$I$4=D$7);$J$2:$J$4))

I suppose your table is in range A1:J4.

Enter list of all 6 fruits in range D7:I7.

Enter "Month" in cell C7 and number of month "9" and "10" in cells C8 and C9.

Maybe you have to replace " ; " with " , " depending on your version of Excel.

Now you can enter above formula in D8 as matrixformula with ctrl+shift+enter.

Then you can copy formula across range D8:I9.

@Sel_excel 

 

To be precise, you're only looking for Apples, and Oranges Only?  If you're looking for quantities to only two Items, where would you like the formula to be situated to account for the total for each item.