Hello All,
I have a attendance report for Jan/Feb/Mar & April'16 but it is in different sheets and I want to count the number off "CO" "PH" "SL" and "AL" that each one has taken.
snp.PNG
Attached is the excel file, I need your help in getting the correct formula.
Thank you for your help.
Regards,
one way (in B5, copied across and down):
=IFERROR(SUMPRODUCT(--(INDIRECT("'"&B$4&"'!$C$3:$C$7")=Summery!$A5)*(INDIRECT("'"&B$4&"'!$A$3:$AH$7")="CO")),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Bertter to adjust to allow for a few more data rows..
=IFERROR(SUMPRODUCT(--(INDIRECT("'"&B$4&"'!$C$3:$C$100")=Summery!$A5)*(INDIRECT("'"&B$4&"'!$A$3:$AH$100")="CO")),"")
Can't attach my resolution as per restrictions on my works pc but type the following formula into the first cell and enter through with Ctrl, shift and enter. Then copy across the grid
=SUM((Jan!$C$3:$C$7=Summery!$A5)*(Jan!$D$3:$AJ$7=Summery!N$3))+SUM((Feb!$C$3:$C$7=Summery!$A5)*(Feb!$D$3:$AJ$7=Summery!N$3))+SUM((Mar!$C$3:$C$7=Summery!$A5)*(Mar!$D$3:$AJ$7=Summery!N$3))+SUM((Apr!$C$3:$C$7=Summery!$A5)*(Apr!$D$3:$AJ$7=Summery!N$3))
by the way if you don't enter the formula (go into the formula and then press the following) through with Ctrl, shift and enter (turning it into an array formula) then it will not work.
Thank you Glenn for Helping it worked.
Dear Steeler Thank you for helping me, it worked.. You guys in this form are amazing
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks