Formula to count entries as per month, year and submission wise

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,

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?

i tried modifying the formula but could not figure what went wrong. Anyway sorry sixthsense to bother you. Thanks for the help.

it did not work but thanks for the reply sixthsense.

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!

Originally Posted by hanidean

I have attached the sheet again with color codes hope this will make you easy to understand what my query is

thanks,

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,

Thank you for the information Sixthsense

Thank you everyone in the forum for being very helpful.

Much appreciated.

Cheers!