SUMIF Totals based on multiple critertia

  • I have a spreadsheet that I would like to create somewhat of a dashboard for, that will count totals for me, based on a few criteria. I am trying to use the SUMIF function, but I can't capture down to level I would like to.


    I have attached a file, so you can see what I am about to explain.


    I have 1 worksheet, that has 4 Components (which are basically 4 different customers). Each have been given quantities of trucks, that where purchased with 3 different types of funding. Once I have shipped and actually signed the trucks off to a component, I update the quantity hand-off column of my worksheet. Then I also record the date of the hand off transaction. The other tab is the worksheet I would like to capture my summary data. ozgrid.com/forum/core/index.php?attachment/69842/


    I would like to count, how many trucks were handed off to a component, under each line of funding, by fiscal year (10/01/xx-09/30/xx)


    Can anyone help me?


    THANK YOU IN ADVANCE!

  • Re: SUMIF Totals based on multiple critertia


    First start by entering the start year of each Fiscal Year in E1 to L1 (e.g 2010, 2011, 2012 and so on). You can hide these by hiding row 1 or changing font colour to white to match background.


    Then in E3 enter formula:


    [COLOR="#0000FF"]=SUMIFS('TRUCK DISTRIBUTION'!$I$2:$I$28,'TRUCK DISTRIBUTION'!$J$2:$J$28,$D3,'TRUCK DISTRIBUTION'!$M$2:$M$28,">="&DATE(E1,10,1),'TRUCK DISTRIBUTION'!$M$2:$M$28,"<="&DATE(E1+1,9,30))[/COLOR]


    copied down and across (after adjusting the ranges to suit actual data).

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: SUMIF Totals based on multiple critertia




    THANK YOU SO MUCH!!! I will try this now! I appreciate the help.

  • Re: SUMIF Totals based on multiple critertia


    NBVC,


    I have ask, is it possible to add one more criteria? I need to able to count how many trucks, under which funding, were fielded to each of the 4 "COMPO" in each fiscal year. What I originally posted only showed 1 COMPO. I have revised and attached my sample spreadsheet. Sorry to bother you, but I truly appreciate your help with this!


    ozgrid.com/forum/core/index.php?attachment/69846/

  • Re: SUMIF Totals based on multiple critertia


    Sorry, not sure I understand. Are you asking to add column B "Compo" to the formula? I don't see any indication of 4COMPO vs. 1COMPO

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: SUMIF Totals based on multiple critertia


    Try, in E3:


    [COLOR="#0000FF"]=SUMIFS('TRUCK DISTRIBUTION'!$I$2:$I$60,'TRUCK DISTRIBUTION'!$B$2:$B$60,$C3,'TRUCK DISTRIBUTION'!$J$2:$J$60,$D3,'TRUCK DISTRIBUTION'!$M$2:$M$60,">="&DATE(E$1,10,1),'TRUCK DISTRIBUTION'!$M$2:$M$60,"<="&DATE(E$1+1,9,30))[/COLOR]


    Remember to adjust ranges to suit (you can use full columns with Sumifs). Copied across and down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: SUMIF Totals based on multiple critertia


    ozgrid.com/forum/core/index.php?attachment/69911/


    Hello NBVC, can I bug you again. I have another one I can't quite figure out. Attached is the sample. There are 2 worksheets.


    On the Summary tab, I want Column N to return the value from Column R on the Detail tab, if the month in column C (summary sheet) and month in column K (detail sheet) match AND if the UIC in column A (summary sheet) and the UIC in column T (detail sheet) match. Does that make sense?


    Also, to add a step further, could I have Column H on the summary tab, count the Qty Shipped from the detail tab, based on if there is a date or if it's blank? Same with the Qty Handed off, count how many have a date or not, still matching the above criteria?


    If it's way confusing, please disregard. I am trying automate so things for my employer, as I will be leaving this office in Oct.


    THANK YOU FOR ANY HELP YOU CAN OFFER! What you have done for me so far has already proved invaluable!!!