Help with conditional SUMPRODUCT

[Sponny]

Hi Everyone - hoping someone can help with a problem I'm having.

Context is a workbook that has 13 sheets in it - the first is a dashboard with summary information and that's what I'm trying to fix. The remaining are months of the year through which I'm tracking a team's holiday entitlement by month and days taken.

The Jan to Dec sheets are fine and I can input the individual persons holiday taken days (it's just 1-31 [month dependent] across the top, with names running down column A) users find their name and input a 1 in the against the relevant column running from 1-31 to show they're taking a day off.

Now, on the dashboard, I have the name, entitlement days, days taken and days remaining to take in one grid. The next grid shows the months Jan-Dec with a count of days taken by month and a total at the end - mock up shown below (assuming the name is in row 3) - AZ1-AZ12 contains text of Jan through Dec:

Name - Entitlement - Taken - Remaining. Jan - Feb - Mar - Apr - May - Jun - Jul - Aug - Sep - Oct - Nov - Dec - Total
Steve 20 10 10 0 0 0 0 3 0 3 0 0 0 0 4 10

The formula I use for the days taken is =SUMPRODUCT(COUNTIF(INDIRECT("'"&$AZ$1:$AZ$12&"'!b3:af3"),1))

The formula in the column under Jan in this example is =SUMPRODUCT(COUNTIF(INDIRECT("Jan!B"&ROW() & ":AF" & ROW()),1)) - [the names in the dashboard are consistently written in the same order in the month tab sheets of Jan-Dec so I can use the ROW() reference as the person "match"]

The problem I have encountered is that if someone takes only half of a day as leave, it's not being picked up as a 0.5 value. I have tried to change the countif to sumif but that hasn't rectified it and I'm at the end of my knowledge of this type of formula.

Does anyone have any ideas how I can fix that so that if someone takes an additional half day in May, say, that it shows as 3.5 under the May column and also as 10.5 in the Taken column?

Any advice and help, gratefully received.

Thanks!

 

[KenU]

I don't have a good way to test this, so I suggest testing the formula below in an unused area of your workbook.

=SUMPRODUCT(INDIRECT("'"&$AZ$1:$AZ$12&"'!b3:af3"))

It appears that the criteria for COUNTIF was set to look for 1.

Hope that helps.

Regards,
Ken

 

[Sponny]

Thanks, Ken, the 1 is required because it's one of the criteria I want it to find - in this case I'm trying to sum any contents that have 1 OR 0.5 in the cell - will try a few combinations with OR and see if I can make it work.