SUMPRODUCT #DIV/0!

Question

Hello,

I'm looking to tally total number of holiday (H) and sickness (S) days taken by staff over a yearly rota. 

Each month separate and appears beneath the last.

The formula I'm using is 

=SUMPRODUCT(($B$9:$B$675=B694)*($C$9:$AD$675="H"))-SUMPRODUCT(($B$9:$B$31=B694)*($C$9:$G$31="H"))

In the example above 'B694' if staff name ref. The second part of the equation is to eliminate the days taken in the last week of this year as that appears on the same rota. 

The problem seems to be that this includes certain rows which denote target and growth for each week, values which populate from different sheet and are at this point unknown.

How do I exclude certain rows from this calculation?

Answer 1

That worked a treat - thank you so much for that.

Oddly the original formula had worked on the same formats for the current year rota and on some sheets in this file but not on others. 

But all sorted now!

Answer 2

NM_XC wrote:

I tried COUNTIFS but this then returns a #VALUE! error

Yes, my bad!  The source of the COUNTIFS #VALUE error is the different "shapes" of the ranges, namely a single column $B$9:$B$675 and the rectangular area $C$9:$AD$675.  No matter....

NM_XC wrote:

I'm afraid I don't follow what you mean by -

=IFERROR(X3/X2 - 1, "")

or

=IF(X2=0, "", X3/X2 - 1)

Where do I incorporate this into the formula?

I was trying to demonstrate the __form__ of formulas that you might use in the cells that return #DIV/0 (and all cells with a similar calculation, even if there is no #DIV/0 error).  My bad again for making too many unexplained assumptions!  No matter....

Try array-entering (press ctrl+shift+Enter instead of just Enter) the following formula:

=SUM(IF($B$9:$B$675=B694, IF(ISTEXT($C$9:$AD$675), --($C$9:$AD$675="H"))))

-SUM(IF($B$9:$B$31=B694, IF(ISTEXT($C$9:$G$31), --($C$9:$G$31="H"))))

Excel displays the formula surrounded with curly braces in the Formula Bar.  Do not type the curly braces yourself.  They indicate that the formula was array-entered.  The double negative ("--") converts TRUE and FALSE into 1 and 0.

(Note:  In Office 365, you might not need to array-enter such formulas anymore.  Or there might be alternative ways to enter such an array formula.  I don't know.)

If you need further assistance, upload an example Excel file that demonstrates the __original__  problem (SUMPRODUCT of ranges that contain #DIV/0 errors) to a file-sharing website, and post the public download URL in a response here.

The Excel file does not have to be the original file, which might contain private information and which might be unduly complex for debug purposes.  It could have fewer rows and columns of data.  And of course, any real names and other personal information should be replaced.

Answer 3

Thank you,

I tried COUNTIFS but this then returns a #VALUE! error, again this seems to trace to the 'growth' row.

I'm afraid I don't follow what you mean by - 

=IFERROR(X3/X2 - 1, "")

or

=IF(X2=0, "", X3/X2 - 1)

Where do I incorporate this into the formula?

Answer 4

IMHO, you should eliminate the #DIV/0 and any other error indictors in the ranges.  For example, a #DIV/0 error can be avoided with:

=IFERROR(X3/X2 - 1, "")

or

=IF(X2=0, "", X3/X2 - 1)

In any case, unless you save the file as "xls" and require compatibility with Excel 2003 or earlier, it is better to use COUNTIFS instead of SUMPRODUCT for this purpose.  Coincidentally, that also ignores #DIV/0 errors et al.

=COUNTIFS($B$9:$B$675, B694, $C$9:$AD$675, "H") - COUNTIFS($B$9:$B$31, B694, $C$9:$G$31, "H")