Hi there,
Could anyone be able to help me with the attached file?
I need to count the entries of the documents monthly wise as per the year(submission date) against document name and the KPI.
I have the answers expected on Sheet 2.
Thanks,
Hi there,
Could anyone be able to help me with the attached file?
I need to count the entries of the documents monthly wise as per the year(submission date) against document name and the KPI.
I have the answers expected on Sheet 2.
Thanks,
In the earlier suggested formula's change these...
"MMM"
to
"MMM 'YY"
Remove this... from the suggested formula's
LEFT(TRIM(B$8),3)
Please do the correction to all the suggested formula's
Last edited by :) Sixthsense :); 05-25-2013 at 02:51 AM. Reason: Added Link Of Another Thread
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
I tried doing it but it gives me a wrong value. More over could you look at the first table on Sheet 2, i need the total entry from all the sheets. how would i do that?
Sorry to say that I can't..... I can just tell you how to do it and I can't apply all the formula in your file for each and every time continuous change in your data structure.
Please try to do it on your own... Or i hope someone will do it for you..........
i tried modifying the formula but could not figure what went wrong. Anyway sorry sixthsense to bother you. Thanks for the help.
This is how the B9 cell formula will be...
=SUMPRODUCT(('CS-CP'!$B$9:$B$501={"Detailed Plan","Detailed Plan & Estidama"})*(TEXT('CS-CP'!$C$9:$C$501,"MMM 'YY")=TRIM(B$8))*ISNUMBER('CS-CP'!$C$9:$C$501))
try to change the rest on your own...
it did not work but thanks for the reply sixthsense.
Try this in B9 :
Formula:Please Login or Register to view this content.
drag across as needed
(Note 1- they values in your table are wrong, there are only 7 Jan 13 submissions, not 9)
(Note 3 - I have put the formula into Columns N-O, but the formula works in B9-D
9 as well)
(note 3 - The trim is there to remove leading/Trailing spaces (which your headers do have, if you edited those out, you could remove the trim functions )
See attached
Hope this helps
EDIT -
not sure what is going on in the next rows, so did not supply formulas for them yet
Last edited by dredwolf; 05-25-2013 at 04:18 AM. Reason: Spelling
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
If you highlight the cells that are out of KPI range so we ca see what should appear, we may be able to offer you a solution...as is stands, your sample shows two extra counts for everything ( there are only 20 2013 data sets..yet all your samples seem to indicate 22 data sets....not sure what is going on...
Dredwolf,
Thanks for the reply. B9=B17+B24+B31 and its the same with C9 which is C9=C17+C24+1 and D9=D17+D24+D31.
I need the formula for B17, B18 and B19.
As advised by Sixthsense earlier below were the formula which would calculate only for Jan 2013 but now there are few enttries for Jan2012 and i need to be separated.
B17 is the entries from CS-CP sheet which contains only Detailed Plan and Detailed Plan & Estidama
=SUMPRODUCT((Sheet1!$B$8:$B$500={"Detailed Plan","Detailed Plan & Estidama"})*(TEXT(Sheet1!$C$8:$C$500,"MMM")=LEFT(TRIM(B$8),3))*ISNUMBER(Sheet1!$C$8:$C$500))
B18 is the entries from CS-CP sheet which contains only detailed plan and detailed plan & estidama but KPI (Row E >=-1)
=SUMPRODUCT((Sheet1!$B$8:$B$500={"Detailed Plan","Detailed Plan & Estidama"})*(TEXT(Sheet1!$C$8:$C$500,"MMM")=LEFT(TRIM(B$8),3))*(Sheet1!$E$8:$E$500<-1))
B19 is the entries from CS-CP sheet which contains only detailed plan and detailed plan & estidama but KPI (Row E <=-1)
=B17-B18
Hope you understood what my query is?
Please let me know if you need more clarifications... Thanks a lot
i made a mistake, Sheet! is referred to "CS-CP"
I have attached the sheet again with color codes hope this will make you easy to understand what my query is
thanks,
Copy of Dashboard.xlsHi,
See attached file for your reference!
Thanks a ton Duanzhuanming
You are the best!
Thanks to Sixthsense and dredwolf
Duanzhuanming,
I have one question. You used =SUMPRODUCT formula for all Cells. except J30, K30, L30 you used =+SUMPRODUCT
May i know what the difference?
Thanks,
Please Refer the below link
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Thank you for the information Sixthsense
Thank you everyone in the forum for being very helpful.
Much appreciated.
Cheers!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks