Excel/Sumproduct query


Hi, I am trying to create a formula to count the number of days holiday taken by staff. This will either be a full day - "HOL" or half day - " Day Hol".

If I do the formula with 1 criteria it returns the correct values, i.e


returns whole numbers &

=SUMPRODUCT(('May16'!$A$2:$A$62=$A2)*('May16'!$B$2:$AF$62=" Day Hol")/2))

will return 0.5 if It finds one " Day Hol" and add them together if there are more than 1 in the range

HOWEVER, I need it to count them all together and I have tried

=SUMPRODUCT(('May16'!$A$2:$A$62=$A2)*('May16'!$B$2:$AF$62="HOL")*('May16'!$B$2:$AF$62=" Day Hol")/2)

thinking it would count them all as they both work individually but it returns no values at all but doesn't give an error either.

Can someone offer some advice, Its been so long time since I have used Excel that i'm no longer sure where I am going wrong, possibly it shouldn't be a SUMPRODUCT formula but I cant figure out what else it could be.

Thanks in advance


your method seems fine, but with a couple of flaws

=SUMPRODUCT(('May16'!$A$2:$A$62=$A2)*('May16'!$B$2:$AF$62=" Day Hol")/2))

should be

=SUMPRODUCT(('May16'!$A$2:$A$62=$A2)*('May16'!$B$2:$AF$62=" Day Hol"))/2

as you want to divide the total number by 2.

To get the total, it then becomes a PLUS calculation

=SUMPRODUCT(('May16'!$A$2:$A$62=$A2)*('May16'!$B$2:$AF$62="HOL")) + (SUMPRODUCT(('May16'!$A$2:$A$62=$A2)*('May16'!$B$2:$AF$62=" Day Hol"))/2)

by using the multiplied value, you have effectively produced a countif with all conditions - which cannot be true all at the same time.

Hope this helps!  You might want to take a look at http://flameenterprises.co.uk/case-studies as there is a holiday planning spreadsheet there
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Aidan Heritage


I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!


My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2017 About.com. All rights reserved.