Convert array-based COUNT..IF to SUMPRODUCT

**ptupper** · 11-14-2012, 08:52 PM

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.

**FDibbins** · 11-14-2012, 08:55 PM

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

**Pete_UK** · 11-14-2012, 08:58 PM

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

Pete

**daddylonglegs** · 11-15-2012, 04:11 PM

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

**ptupper** · 11-16-2012, 12:46 AM

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.

**daddylonglegs** · 11-16-2012, 04:36 PM

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?

**ptupper** · 11-16-2012, 07:01 PM

<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!!!