Count Or Sum Two Criteria From Multiple Sheets

  • I am trying to count data from multiple sheets based on two criteria. The sheets are all formatted the same and represent shipping schedules. The first is a text match to a specific customer name and the second is to count based on a numeric value which can be -1,0,1,2,3+ representing order lead time in days. The lead time is calculated for each row on each worksheet per NETWORKDAYS function based on order date versus ship date.


    Sheets 1-4 are the schedules and sheet 5 is a summary sheet to measure the order lead time per week and per customer. On summary sheet, column A has customer names, column B is total orders for that customer from sheets 1-4 (Weeks 1 – 4). Column C needs to display # of 0 days lead time orders, column D needs to display # of 1 day lead time orders, column C needs to display # of 0 days lead time orders, column E needs to display # of 2 days lead time orders, column F needs to display # of 3 days or more lead time orders. On the summary sheet, the upper table displays only the order lead time data in the respective columns in weekly buckets. I need the lower table to display the data per customer in the respective columns (0, 1, 2, 3+ days lead time). I also need it to tally MTD (Month To Date) and to ignore if column F is blank even though -1 shows in column I due to formula. See Week 4 on summary sheet – 12 displayed in column C but should be nothing/blank as there are no orders in Week 4 yet.


    I have tried SUMPRODUCT and COUNTIF and even some nesting but I cannot seem to get the syntax or function combinations right. I got it to work on one sheet but not multiple sheets. Thanks in advance for your kind assistance.


    DEF98

  • Re: Count/sum with two criteria from multiple sheets


    Neither of those functions work across multiple worksheets. It has to be more indirect.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

  • Re: Count/sum with two criteria from multiple sheets


    Sorry, it takes several steps to get this to work. I'm trying to find the discussion where this is addressed. I'll try to post the link.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

  • Re: Count/sum with two criteria from multiple sheets


    This is a post that had been elsewhere but seems to apply
    -------
    If you only have one criteria like in a sumif and either sum the same range
    or another range you can use this:
    First you need to create a list with ALL (not like in 3-D excel first and
    last) sheet names or if they have the same aplha name plus numbers you can do
    it as well.
    1. if they all have different names and you put all sheet names in range
    H1:H20
    and let's say you want to sum B1:B100 in all sheets where A1:A100 is criteria1
    then you can use this


    =SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$20&"'!A1:A100"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B100")))



    if the names are identical text plus different numbers like Sheet1, Sheet2
    amd so one then you can use


    =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!A1:A100"),"criteria1",INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!B1:B100")))


    if you need more criteria and more ranges may I recommend Morefun by Laurent
    Longre


    It's an excellent add-in that has some nice 3-D features



    Regards,


    Peo Sjoblom
    --------------


    Hope this helps.


    BTW, The add-in that Peo mentions is well-worth it. I use it for several, although never for this. I am still learning its capabilities.


    http://xcell05.free.fr/


    HTH


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

  • Re: Count/sum with two criteria from multiple sheets


    Here is Aladin's take on this, also mentioning the add-in.


    --------------------


    If you just want to sum B1 across Sheet1 to Sheet3...


    =SUM(Sheet1:Sheet3!B1)


    If you just want to count numbers B1 houses across Sheet1 to Sheet3...


    =COUNT(Sheet1:Sheet3!B1)


    If you want to sum B1 when B1 > 0 (exluding negative values) across Sheet1
    to Sheet3...


    [1] If you have the morefunc.xll add-in installed:


    For Conditional Summing:


    =SUMPRODUCT(--(THREED(Sheet1:Sheet3!B1)>0),THREED(Sheet1:Sheet3!B1))


    For Conditional Counting:


    =COUNTIF.3D(Sheet1:Sheet3!B1,">0")


    [2] If you need to do it without such an add-in:


    For Conditional Summing:


    =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!B1"),">0"))


    For Conditional Counting:


    =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!B1"),">0"))


    where Sheets refers to a range housing the relevant sheet names, that is,
    Sheet1, Sheet2, and Sheet3.


    --------------------------


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

  • Re: Count/sum with two criteria from multiple sheets


    Quote from shades

    Neither of those functions work across multiple worksheets. It has to be more indirect.


    Just saw this comment and have not digested the rest of the thread, but wanted to mention that SUMPRODUCT does work accross sheets if you used named ranges instead of actual cell addresses. The attached has named ranges "rng1" and "rng2" for A1:A10 on sheet1 and C1:C10 on sheet2. The formula in cell A1 on Sheet3 uses a SUMPRODUCT with these named ranges.

  • Re: Count/sum with two criteria from multiple sheets


    I hate to write this: are you sure, Thomas? Just playing with it, it doesn't seem to give the correct result.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

  • Re: Count/sum with two criteria from multiple sheets


    Shades,


    Thanks very much for your research efforts and input. Unfortunately, I could not get the examples to work for me. Even tried the one from Thomach and couldn't make that work either. :? I have reverted to sub-tables on each sheet to summarize the local figures first and then I'll pull the totals to the monthly summary worksheet. Extremely basic but it works.


    Do you have experience with the MoreFunc 3D add-in? I am not familiar with it but the examples look interesting and I may have a number of applications for the functionality.


    Thanks again for your time,
    DEF98

  • Re: Count/sum with two criteria from multiple sheets


    I have used a few of the functions available with morefunc add-in. It is a very handy tool/resource.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

Participate now!

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