Troubleshooting Sumproduct and Countif formula

Hello, I was hoping for some help trouble shooting a formula. I am trying to count the number of vacation days for each person on the Jan worksheet using this formula. For some reason, it is counting all of the vacation days, and not just the vacation days for one person. Any help would be greatly appreciated. The formula is on the vacation sheet in cell D3.



=(SUMPRODUCT(--(jan=Input!$B18)--(jan=A3))-COUNTIF(jan,A3))

Thanks!
Clayton Grove

Hi,

Try this,

=SUMPRODUCT(--(jan=Input!$B18),--(jan=A3))+COUNTIF(jan,A3)

Thanks for the help bit it is not counting the instances that vacatio. Is showing up, it is counting the instances of the names. I need it to count how many times "Vacation" shows up in each row per name on the sheet.


Thanks again!

Clayton

Protected sheet....not cool.

Because of the fact you're using named ranges where named range encompasses both conditional arrays (D,E:K) and the fact you're looking in arrays of different sizes I think you need something like:

=SUMPRODUCT((INDEX(jan,0,2):INDEX(jan,0,8)=Input!$B18)*(INDEX(jan,0,1)=A3))

I would also be VERY wary of using named ranges where name = sheet name... invariably this will cause you headaches in the long run.

If you don't quantify the INDEX ranges you will end up with an answer of 0, eg:

=SUMPRODUCT((jan=Input!B18)*(jan=$A3))

will = 0 because you would need a name = Input!B18 to ever get a true result... given name <> absence type the first record in the array will always be FALSE and thus regardless of any other results the PRODUCT of that row in the array will be 0.

No problem -- please mark as SOLVED