[SOLVED]Count data monthly automatically

Hey guys!

I need your help, i need a formula that i can put in cells that can count datas automatically each month.
The data that i have is huge and it updates everyday. As an example i will explain it simply.

Lets say i have a list of product groups in column A. Column B is the date where the samples arrived, Column C is the approval, Column D is the sending date.

I probably need to post a picture of the table:
http://img845.imageshack.us/img845/8855/tableog.png

sample workbook:
Attachment 197668Attachment 197668



So as you can see, i need to a formula to count the percentage of "approved samples", "not approved samples" and so on (for month december)

The approved sample for month december should have these requirement: If the date of arrival is in December 2012, if the approval says "approved" and NOT EMPTY,if the date of sending is NOT EMPTY.. So from the table, the answer is 2.
The percentage should be the 2, divided by all samples (Telephone) arrived with approval, doesnt matter if the date of sending is empty or not. (which is 4)

I hope i didnt make it too confusing, and i would want a formula that can do this automatically monthly, so i dont need to do the formula time and again.
and i really hope someone can help me, id appreciate it!

Attach a real sample workbook.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

Hi to you.

Very nice picture! , BUT:

Can you work on a picture? Pls upload a small sample workbook.

Maybe.

=SUMPRODUCT((C2:C15<>"")*(D2:D15<>"")*(C2:C15="approved")*(MONTH(D2:D15)=12))

That formula looks good! but i forgot to mention, another criteria is that the percentage i want for each product, which means:

1.Percentage of approved Telephone
2.Percentage of approved Pen
etc..

And can i also use the whole column C:C or D:D? will the formula work as well?

COuld you tell me what this formula do? (C2:C15<>"")

it also seems that i have to change the formula *(MONTH(D2:D15)=12)) every month too. Is this the only possible way? or is there another alternative to it, so it does it automatically, taking account of today's date and month and link it to the formula?

Sorry for asking so many questions, but i hope its not too troublesome!

=SUMPRODUCT((C2:C15<>"")*(D2:D15<>"")*(C2:C15="approved")*(MONTH(D2:D15)=12)*(A2:A15="Telephone"))

1) In Excel >=2007, you can use whole column range, but my opinion is to don't do this because the calculations will be slower.

2) My suggestion is to use a validation list with products and use a cell reference in the formula.

Just now i work to a non strong pc.

Trying to test what you asked, my pc blocked for more than 10 minutes.

As i told my suggestion is to DON'T use whole column range.

Use OR large ranges(as let's say A2:A15000) OR use dynamic ranges.

http://www.contextures.com/xlNames01.html

Originally Posted by patehi

hi Fotis!

im sorry that your pC did that >.<
Okay i tried your suggestion and it works nicely ^^

thank you!

Never mind!

You are welcome.

One way.

=SUMPRODUCT((C2:C15<>"")*(D2:D15<>"")*(C2:C15="approved")*(MONTH(D2:D15)=4)*((MONTH(D2:D15)=5))*((MONTH(D2:D15)=6)*(A2:A15="Telephone"))

As my IP is blocked, i am not able to send a sample. But try this.

In A11 and copy until A37, put this.

=MONTH(C11) Column A will be a helper and hidden if you like column.

Then IN C5, put this

=SUMPRODUCT(($B$11:$B$37=C$4)*($D$11:$D$37=$B5)*($A$11:$A$37>=$C$2)*($A$11:$A$37<=$E$2))

Copy to C6,D5,D6.

You are welcome.

Thanks for the reb*