Search code examples
exceldatedate-rangesumifs

Excel - I need help to reduce redundancy of SUMIFS with date and keyword match

I am trying to gather the total cost of goods sold on various items as the products are sold, and grouping them by month to update a spreadsheet.

The SUMIFS formula I am using is extremely repetitive, due to the date constrictions, and I know there has to be an easier and cleaner way to produce the same results.

The formula I am currently using:

=SUM((SUMIFS($B:$B,$A:$A,">="&DATE(2016,1,1),$A:$A,"<="&DATE(2016,1,31),$C:$C,"Apple")*0.5)+(SUMIFS($B:$B,$A:$A,">="&DATE(2016,1,1),$A:$A,"<="&DATE(2016,1,31),$C:$C,"Banana")*0.75)+(SUMIFS($B:$B,$A:$A,">="&DATE(2016,1,1),$A:$A,"<="&DATE(2016,1,31),$C:$C,"Orange")*1.25))

Thank you in advance!

Solution

  • You could use an array formula. Enter the following formula and hit. CTRL + SHIFT + ENTER:

    =SUM(SUMIFS($B:$B,$A:$A,">="&DATE(2016,1,1),$A:$A,"<="&DATE(2016,1,31),$C:$C,{"Apple","Banana","Orange"})*{0.5,0.75,1.25})