Oct 05 2021 03:37 AM
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
Oct 05 2021 04:21 AM
See the attached version.
Oct 05 2021 03:54 PM
=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.
Oct 06 2021 12:31 PM
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.