Hi everyone,
I am currently doing a leave calendar for my department and I cant seem to add up the number of days of leave they took in each month. For the leave calendar, they have to choose their name and whether they want a half day, full day leave or reservist. Half day=0.5, Full day=1, reservist = 0
Thank you in advance!
In H2
=SUMPRODUCT((INDIRECT(H$1&"!$A$3:$M$5")=Table1[@Names])*(((INDIRECT(H$1&"!$b$3:$n$5")="Half Day")*0.5)+(INDIRECT(H$1&"!$b$3:$n$5")="Full Day"))*((INDIRECT(H$1&"!$b$3:$n$5")<>"Reservist")))
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Hi,
How do I extend the formula for the whole calendar in the month? Cos I tried to input it in the other cells below but it does not get added up.
Thanks.
Change $5 to $25
=SUMPRODUCT((INDIRECT(H$1&"!$A$3:$M$25")=Table1[@Names])*(((INDIRECT(H$1&"!$b$3:$n$25")="Half Day")*0.5)+(INDIRECT(H$1&"!$b$3:$n$25")="Full Day"))*((INDIRECT(H$1&"!$b$3:$n$25")<>"Reservist")))
I've done that but it couldnt add up. It remains as a "-"
Pl see file.
Revised formula in H2
Tip:=SUMPRODUCT((COUNTIF(INDIRECT("Table1[@Names]"),INDIRECT(H$1&"!$A$3:$M$25")))*(((INDIRECT(H$1&"!$b$3:$n$25")="Half Day")*0.5)+(INDIRECT(H$1&"!$b$3:$n$25")="Full Day"))*((INDIRECT(H$1&"!$b$3:$n$25")<>"Reservist")))
In Monthly sheets formula is
=((COUNTA(Team!A2:A10)-(COUNTIF(B3:B5,"full day")+COUNTIF(B3:B5,"half day")))/COUNTA(Team!A2:A10))
It should be replaced
=((COUNTA(Team!$A$2:$A$10)-(COUNTIF(B3:B5,"full day")+COUNTIF(B3:B5,"half day")))/COUNTA(Team!$A$2:$A$10))
Otherwise When formula is copied it creates problem.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks