# Sum non-consecutive/contiguous cells by Month and Year

• I have a spreadsheet with 3 repeated rows - Quantity, Total Price, Unit Price. Next to Quantity is a cell with the date. In my example A10 = Date, C10 = Quantity, C11 = Total Price, C12 = Unit Price. I am looking for a formula to sum all Quantity values. For the sake of trying to get the formula right, I have been using two sets of data A10:C15...but this will go on without end.

I can get an array formula to sum by month ={SUM(IF(MONTH(A10:A15)=11,C10:C15,""))}
I can get an array formula to sum by year {=SUM(IF(YEAR(A10:A15)=2016,C10:C15,""))}

I cannot get one to sum by both...I have tried, without success ={SUM(IF(AND(MONTH(A10:A15)=11,YEAR(A10:A15)=2016),C10:C15,""))}

(also...my first post...hopefully I met all the rules/criteria for a good post!)

TC

• Re: Sum non-consecutive/contiguous cells by Month and Year

Pivot Table sounds like a good choice for this.

=SUMIFS(\$C\$10:\$C\$15,\$A\$10:\$A\$16,">=11/1/2016",\$A\$10:\$A\$15,"<=11/30/2016")

You could use cell references for the dates rather than hard coding them into the formula
=SUMIFS(\$C\$11:\$C\$16,\$A\$11:\$A\$16,">="&E9,\$A\$11:\$A\$16,"<="&\$F\$9)

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Sum non-consecutive/contiguous cells by Month and Year

Great...thanks very much for your help. I am self taught and don't know much (anything) about pivot tables. I see them mentioned a lot in forums though. I think I have some homework to do!

Cheers

• Re: Sum non-consecutive/contiguous cells by Month and Year

Here is a tip - one of the best features, IMHO, in Excel is the Evaluate Formula Tool found on the Formulas tab in the ribbon.
This tool lets you step through a formula and see how each argument is evaluated - excellent for learning. Of course, you do need to know something about the function syntax and what is required in the argument but with all the built in help in Excel this, too, is easy to learn.

To use the tool simply select any cell with a formula then click Evaluate Formula

For Pivot Tables I highly recommend a visit to YouTube and just search on them. Lots of good help there for nearly anything you want to do in Excel.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Sum non-consecutive/contiguous cells by Month and Year

Great...will have a look later today. Thanks all