Count Formula based on criteria

[pauldaddyadams]

Hi,

I need some help with a formula that will count the number of projects in a particular week and month.

On the attached when the current week number is entered I would like a formula to count the number of UNIQUE projects occurring in that week based on the data table. The data table displays the days on the project so if it is more than zero then assume the project is being worked on.

I then need a similar formula which will count the number of UNIQUE projects in a month.

I have highlighted the word unique as say for example in week 2 and 5 (both November months) the count would be 1 as this is the same project in the example (Project 8).

Can anyone help please?

I have attached an example along with the desired results required.

Paul

[Jockney]

I've added in some additional rows to save doing an array formula and slowing the whole thing down.

Let me know if you need anything else and if I've helped please give my reputation star a click

Cheers

[pauldaddyadams]

Hi

Ideally I do not want to use helper rows but will if I have to.

The formula is summing but I would like it to count (but only count unquite projects). Does that make sense?

[Punnam]

Please Login or Register  to view this content.

for For D3 Row(41), Row(17) should be updated based on your data one time

Punnam

[pauldaddyadams]

Hi,

That worked great for the current week count - thank you!

Would anyone be able to help with the monthly count formula - the red cells.

[Punnam]

Please Login or Register  to view this content.

Ctrl+Shift+Enter after copying the Formula
try this in D8 may be lengthy one
Thanks for the feed back & reps

Punnam

[pauldaddyadams]

Hi,

Its close!

It doesnt work for November as it gives a result of 10, it should be 7. This is because it only needs to count unique projects. e.g. The formula would count project 3 in november 3 times but I only need this to say 1.

Is it possible?

[Punnam]

Hi pauldaddyadams

I need some time work on this

Punnam

[pauldaddyadams]

Hi,

Thanks Punnam

Can anyone help with this formula to put me out of my misery?

[pauldaddyadams]

Hi,

I need to add an IF statement...

I have made some progrees with this - this array formula works:
=SUM(IF(FREQUENCY(IF($D$16:$D$40>0,IF(C16:C40<>"",MATCH(C16:C40,C16:C40,0))),ROW(C16:C40)-ROW(C16)+1),1))


However I have to change the range to reflect the month. Is there any way I can add to the formula to match the month. I have read here:

That it can be done but I can not get it to work.

Its the red cells on the attached