Hii Guys,
Need a formula in which I can auto calculate monthly department wise counts, for e.g Dept AAA - Apr (Month) - 8 (Per Day)* 6 (Working Weekdays) = 48 like this i need for all the department
Hii Guys,
Need a formula in which I can auto calculate monthly department wise counts, for e.g Dept AAA - Apr (Month) - 8 (Per Day)* 6 (Working Weekdays) = 48 like this i need for all the department
You can add reputation(s) of those who helped
Try this in F20:
=SUMPRODUCT((TEXT($D$5:$V$5,"mmm")=F$19)*OFFSET($D$6:$V$6,MATCH($E20,$C$7:$C$11,0),))*6
Copy across and down
Put this in F20, drag down and across
=SUMPRODUCT(($E20=$C$7:$C$11)*(MONTH(1&F$19)=MONTH($D$5:$V$5))*$D$7:$V$11*$D$4:$V$4)
Cheers!
Deep Dave
Hii Guys,
Thanks for Your help..but need some corrections , AAA dept Apr month is 48 but May month will be like this (8*6)+ (4*6)+(4*6)+(3*6)+(3*2)=120 but both the formulas calculate total has 132.
How is the last one (3*2)? :O
Can you tell me the cell references?
Column I6 (30-MAY).. it goes like this...30th May, 31st may (End of May month), (June starts from here)01st June, 02nd June 03rd June 04th June 05th June so I7 will be multiplied by only 2 days for the month of may not by 6days, the formula is multiplying by 6days
re-arange the data so that the table is in a verticaly way.
after that use a pivot table.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Didnt understood about rearranging data.. can u pls explain the same in attachment...Thanks
f20TRY THIS AND COPY ACROSSPlease Login or Register to view this content.
Note:- Please change June as Jun and July as Jul
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
with a pivot table.
see the attached file.
It doesnt count as per month...i need counts as per month & department wise
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks