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,""))}


    Any ideas? Thanks in advance.


    (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)

    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


    Glad to be of help.
    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login