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, 26 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...

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, 14 views)