sum every nth cell with additional criteria

[rogowicz]

I have a system generated table that displays sales data for products. Image below. It has a merged cell in the first row with a month/year, which has the quantity and sales dollars for that month. I want to consolidate this data by year rather than by each month.

I know how to sum every nth cell to get only the Qty or only the Amt =SUM(IF(MOD(ROW($B$1:$B$15),2)=0,$B$1:$B$15,0))

I know how to sumif based on date range (several ways to do this)

I do not know how to combine those two functions. I need this today if at all possible, but no expectations. Can anyone help?

FYI I have 300+ rows of data and 5 years that I want to consolidate.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(MOD(ROW($B$1:$B$15)-ROW($B$1),2)=0,$B$1:$B$15))

will sum values from B1:B15 at every other row.


If the dates are in C1:C15 and they must equal say X1, a particular date...

Control+shift+enter, not just enter:

=SUM(IF(MOD(ROW($B$1:$B$15)-ROW($B$1),2)=0,IF($C$1:$C$15=X1,$B$1:$B$15)))