+ Reply to Thread
Results 1 to 7 of 7

Convert array-based COUNT..IF to SUMPRODUCT

  1. #1
    Registered User
    Join Date
    08-24-2008
    Location
    TX
    Posts
    3

    Convert array-based COUNT..IF to SUMPRODUCT

    This should be simple but I have a mental block with properly setting up SUMPRODUCTs and when to use , + * or --

    I want to convert the following to a properly formatted SUMPRODUCT so I can easily add additional arrays like one to exclude holidays/vacation... or (<>DaysOff)
    = COUNT(IF(WEEKDAY(INDIRECT(C33),2)<5,1))*9 + COUNT(IF(WEEKDAY(INDIRECT(C33),2)=5,1))*8

    p.s. This gives me total work hours in a month (INDIRECT("month")) since M-TH are 9 hrs and FR only 8 hrs.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,901

    Re: Convert array-based COUNT..IF to SUMPRODUCT

    You're profile does not indicate whch version of excel you have (the reason that is asked for, is that it can affect suggestions offered to you), but i will still make the suggestion assuming you have 2007 or higher...have you tried to use countifS() instead of just countif()? you may be able to get around using a sumproduct if you want
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,607

    Re: Convert array-based COUNT..IF to SUMPRODUCT

    You are only looking at a single cell, C33, so where does the "array" part of it come in?

    Pete

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Convert array-based COUNT..IF to SUMPRODUCT

    Try defining the value for each day separately like this

    =SUMPRODUCT((WEEKDAY(INDIRECT(C33))={1,2,3,4,5,6,7})*{0,9,9,9,9,8,0})

    Now say you have "off" in the next column to the right then this formula will exclude the "off" days from the calculation [I'm assuming that the range derived from INDIRECT(C33) is a column of values]

    =SUMPRODUCT((OFFSET(INDIRECT(C33),0,1)<>"Off")*(WEEKDAY(INDIRECT(C33))={1,2,3,4,5,6,7})*{0,9,9,9,9,8,0})
    Audere est facere

  5. #5
    Registered User
    Join Date
    08-24-2008
    Location
    TX
    Posts
    3

    Re: Convert array-based COUNT..IF to SUMPRODUCT

    Sorry, posting noob... Maybe the xlsm here will help??? Versions 2007 & 2010... INDIRECT(C33) referred to a MONTH which is a named range of dates

    Eventually, I want for the user to be able to calculate the number of required WORKING hours for the chosen schedule ("A", "B" or "C") and dynamic YEAR is set using the slider.

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Convert array-based COUNT..IF to SUMPRODUCT

    Yes, my suggested formula would work for a single column of dates, but not for your month ranges - the way you have it set up makes it quite difficult because of the "null strings" in the range - when you use WEEKDAY on one of those, for instance, an error is returned so you need a function that can cope with that, COUNT in your original formula, for example.

    Can you be clear about what you need to calculate? Do you want the hours for each shift for each month (excluding holidays, weekends etc.)? You mention a "period" from 18th to 17th, do you need to calculate that too? It may be simpler just to calculate the hours with reference to the start and end date of the month in question rather than looking at the actual range on the calendar.

    Do you need formulas that will work in Excel 2007, NETWORKDAYS.INTL function might make the task easier but that's only available in Excel 2010?

  7. #7
    Registered User
    Join Date
    08-24-2008
    Location
    TX
    Posts
    3

    Re: Convert array-based COUNT..IF to SUMPRODUCT

    <UPDATED> I'd prefer to have Excel 2007 formulas so I can share... very few have 2010.

    I am trying to present a 1 page dynamic calendar (choose year & schedule) with company holidays & Fridays off (sch "A" & "B") and calculate/display the required working hours of each monthly "period" from 18th to 17th. I was trying to minimize the "helper" columns and minimize the mess with creative formulas or maybe a custom udf.

    I tried to make all named ranges dynamic for flexability but have encountered issues with nulls and displaying New Year's correctly if it's "pushed" into the prior year.

    This version is much clearer and closer to what I am looking to wind up with!!!
    Attached Files Attached Files
    Last edited by ptupper; 11-16-2012 at 09:29 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1