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

1. ## 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,

2. ## Re: Formula to count entries as per month, year and submission wise

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

3. ## Re: Formula to count entries as per month, year and submission wise

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?

4. ## Re: Formula to count entries as per month, year and submission wise

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..........

5. ## Re: Formula to count entries as per month, year and submission wise

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

6. ## Re: Formula to count entries as per month, year and submission wise

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...

7. ## Re: Formula to count entries as per month, year and submission wise

it did not work but thanks for the reply sixthsense.

8. ## Re: Formula to count entries as per month, year and submission wise

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

9. ## Re: Formula to count entries as per month, year and submission wise

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...

10. ## Re: Formula to count entries as per month, year and submission wise

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

11. ## Re: Formula to count entries as per month, year and submission wise

i made a mistake, Sheet! is referred to "CS-CP"

12. ## Re: Formula to count entries as per month, year and submission wise

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

thanks,

13. ## Re: Formula to count entries as per month, year and submission wise

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,

14. ## Re: Formula to count entries as per month, year and submission wise

Thanks a ton Duanzhuanming

You are the best!

Thanks to Sixthsense and dredwolf

15. ## Re: Formula to count entries as per month, year and submission wise

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,

16. ## Re: Formula to count entries as per month, year and submission wise

Originally Posted by hanidean
You used =SUMPRODUCT formula for all Cells. except J30, K30, L30 you used =+SUMPRODUCT, May i know what the difference?

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

17. ## Re: Formula to count entries as per month, year and submission wise

Thank you for the information Sixthsense

Thank you everyone in the forum for being very helpful.

Much appreciated.

Cheers!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1