Excel - Sum row with conditions depending on filter at bottom of spreadsheet

Question

I'm trying to create a summing formula that will sum a row but depends on a condition specific to each cell. I have some cells where there are different types of costs, and the user inputs how many of that cost were used on each day of the week.

I want to sum the total number of units used for each week by type, but the catch is that there is a drop down menu at the bottom of the sheet for each week there the user selects yes or no. If they select no, then I want to sum that day as normal. If they select yes, then I want divide the number of units used that day by two and sum that.

So for example, if day one has 2 units and says no, and day two has 2 units and says yes, and no other days that week have units used then the total for that week would be 3 units.

I could write out a long formula with lots of if/else conditional statements, but I was wondering if there is a simpler formula I could use to do this. I considered using a SUMIF but couldn't find a way to make that work.

Answer 1

You can use SUMIFS() with some arrays that will count the Yes's twice then divide by 2

=SUM(SUMIFS($B4:$H4,$B$14:$H$14,{"Yes","Yes","No"}))/2

This does 3 SUMIFS. Adds the results then divides by 2.

This assumes the unit count starts in row 4 column B to Column H and that the Yes/No is in the same column in row 14.