Counting Dates + Sumproduct

[cooh23]

Hello all,

I have a spread sheet that contains several columns but 2 columns are dates.
This represents the stage in which the project is on.

Stage 1 project start dates are on column 1 and column 2 contains the dates completed.

I want to be able to count how many projects I started today and how many did i finish.

Column 1 = Contains start date
Column 2 = Contains finish date

Goal = count Column 1 and Count column 2.

I want to count how many I start and finish daily, weekly, and monthly.

Thank you for your help.

John

[cooh23]

Hello all,

I have a spread sheet that contains several columns but 2 columns are dates.
This represents the stage in which the project is on.

Stage 1 project start dates are on column 1 and column 2 contains the dates completed.

I want to be able to count how many projects I started today and how many did i finish.

Column 1 = Contains start date
Column 2 = Contains finish date

Goal = count Column 1 and Count column 2.

I want to count how many I start and finish daily, weekly, and monthly.

Thank you for your help.

John

[Richard Schollar]

Hi John

Assuming A1:A100 contains Start, B1:B100 contains End, then in C1:D1 you write the limits you want to summarise by (eg Start date you want to count in C1, when those projects must have been completed by in D1) then a formula you could use to count:

=SUMPRODUCT(--(A1:A100>=C1),--(B1:B100<=D1),--(B1:B100<>""))

the last term is required to exclude blanks (ie unfinished projects).

Hope this helps!

Richard

[cooh23]

Hi All,

I have been trying to figure out this formula and have been unsuccessful. I have tried searching the forum for the sumproduct formulas to count dates but non of the seem to work.

I have attached a sheet of what I want for it to do.

For example, I want to count how many times a specific date appeared in Stage 1 and show the total of the date under the specific date for where it appeared.

I have manually entered the number of times a specific date into the sheet to show you guys how I want this to appear.

your help is greatly appreciated. THank you.


John

Please Login or Register  to view this content.

[VBA Noob]

cooh23,

Please read forum rules below and stick to the orginial thread. I've merged them this time

See if the attached helps

VBA Noob

[cooh23]

cooh23,

Please read forum rules below and stick to the orginial thread. I've merged them this time

See if the attached helps

VBA Noob

thank you so much.. this is exactly what I was looking for.

I am trying to understand your codes but I am a bit confused. If it's not too much to ask, would you be able to provide an explanation of how the codes exactly works. I will be doing this for the month January - December.

[VBA Noob]

Have you read the rules ??

VBA Noob

[cooh23]

I just read the rules. I apologize for posting multiple times.

I have also played around with the codes and got it to calculate based on the year as well. I know understand how the code works.

Here's the code:

Please Login or Register  to view this content.

Again, thank you so much for your help. I really appreciate it.

John

[VBA Noob]

Sumproduct may slow your workbook down so a pivot table may be a better option

VBA Noob