Hi all,



I'm stuck with a calculation on weekend allowance. Workers would be paid a set allowance if they have worked on Sundays.

I have a spreadsheet set up to calculate the payment for an entire month. The dates lie on D2:AH2 and the number of hours worked per person are directly below, (e.g. D6:AH6,D7:AH7, etc). The number of hours worked would show 'A' if the person if absent.

To calculate the number of Sundays worked, I have tried SUMPRODUCT(--(D6:AH6>0),--(WEEKDAY($D$2:$AH$2)=1)), but it seems to give me 4 every time. Any ideas?

Can you upload an example file, with just four personnel?

If you can, I will see what I can do!

Sure thing.

Attached Files

Employee Snippet.xls (30.0 KB, 24 views)

Good Morning,

Well, it took some thinking about because I do not know how to deal with arrays. Attached you will find a file, that although not an elegant solution, is a least a solution to your problem.

What I have done is created a range for Week_01, then using a horizontal look up, looked down to see whether the Sunday cell was an 'A' or zero and giving a result of 0, or 1. You will need to do the same for the remaining weeks.

Hope this helps.

Attached Files

Nutrastat Employee Snippet (1).xls (42.5 KB, 12 views)

Usually. it is a bad idea to mix in a table numbers and text
Maybe you can get rid of the "A" in this table, and your formula will work, with a little modification
your formula evaluates the range , and counts how many cells are >0 (and no mattes what values you have in D9:AH9...
=SUMPRODUCT(--(D9:AH9>0);--(WEEKDAY($D$3:$AH$3)=1))
that is why, you get 4 as the result, for excel the "A" cells are >0 , and the result means that there are 4 cells in the sum range for sunday...
try instead of "A" leave the cells blank (and make your conditional formatting formula to mark the blanks with red), and remove >0 from formula, you will get the number of hours worked in sundays...
try this:
=SUMPRODUCT(--(D7:AH7);--(WEEKDAY($D$3:$AH$3)=1))

@nutrastat: That works!

And yeah - The letters are actually causing the troubles with the formula. I'll see if I can change it without breaking something.

Thanks all!

well, i guess you can mark your thread as solved...