 # Help with sum ifs formulas

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

# Re: Help with sum ifs formulas

See the attached version.

# Re: Help with sum ifs formulas

=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.

# Re: Help with sum ifs formulas

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.