Return count of occurences of dates excluding those within a same package number

[Phil13]

Good morning,

I was wondering if someonecould give me some help on a formula.

First of all, a bit ofbackground.

I have 2 tabs

Tab 1 contains data presentingdifferent projects, sometimes pack of the same package.

	A	B	C	D	E
1	Project no	Project title	Project start date	Project Finish
2	01	Title 1	01/01/2019	01/05/2019
3	01	Title 2	01/01/2019	01/05/2019
4	01	Title 3	01/01/2019	01/05/2019
5	02	Title 4	01/01/2019	01/06/2019
6	03	Title 5	01/02/2019	01/09/2019
7	03	Title 6	01/03/2019	01/08/2019

<tbody> </tbody>

Tab 2 contains another tablethat counts the number of occurrences of an event (such as Project start dateor Project Finish), with the below formula:

	A	B	C	D	E
1		January	February	March	April
2	Project Start
3	Project Finish
4
5
6
7

<tbody> </tbody>

From column B to E I have a formulathat would count the occurencies of an event such as Project Start or ProjectFinish
For January the formula wouldlook like this for this example:
=SUMPRODUCT(1*(MONTH('Tab 1'!$B$2:$B$7)=1)*(YEAR('Tab 2 '!$B$2:$B$7)=2019))


What works so far: theformula does pick up every occurrences happening in a specific month, but Iwould like now to go one step beyond and ask the formula to only count one occurrenceper Project no.


For example, for Project no01 there are 3 different project titles, and currently the formula will count 3different projects. BUut would like the formula to only return 1.



My question to you: how can Iask excel to look up first at the project number and count one occurrence anddiscard a new occurrence within the same Project no off the calculation?

 

[WassimN]

Hi Phil13,

How about counting the number of occurrences and subtract from it to get 1. You may look into the COUNTIF() or the COUNTIFS() functions, and then subreact x - (x-1)

Do you think this will help?

 

[Marcelo Branco]

Phil,

Are these the expected/desired results?

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​		January​	February​	March​	April​	May​	June​	July​	August​	September​
2​	Start Date​	2​	1​	1​	0​	0​	0​	0​	0​	0​
3​	Finish Date​	0​	0​	0​	0​	1​	1​	0​	1​	1​

M.

 

[Phil13]

Yes, Marcelo

 

[Phil13]

I am open to any suggestion, although I am not really sure how to do that.

 

[Phil13]

I am open to any suggestion, although I am not really sure how to do that.

This was meant to be a response to WassimN

 

[Marcelo Branco]

Yes, Marcelo

Maybe this...

Tab 2

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

1​

Year​

2019​

2​

Month​

January​

February​

March​

April​

May​

June​

July​

August​

September​

October​

November​

December​

3​

Start Date​

2​

1​

1​

0​

0​

0​

0​

0​

0​

0​

0​

0​

4​

Finish Date​

0​

0​

0​

0​

1​

1​

0​

1​

1​

0​

0​

0​

<tbody>
</tbody>

Year in B1

Formula in B3 copied across
=SUMPRODUCT(--(MATCH('Tab 1'!$A$2:$A$7&"|"&'Tab 1'!$C$2:$C$7,'Tab 1'!$A$2:$A$7&"|"&'Tab 1'!$C$2:$C$7,0)=ROW('Tab 1'!$A$2:$A$7)-ROW('Tab 1'!$A$2)+1),--(MONTH('Tab 1'!$C$2:$C$7)=COLUMNS($B3:B3)),--(YEAR('Tab 1'!$C$2:$C$7)=$B$1))

Formula in C3 copied across
=SUMPRODUCT(--(MATCH('Tab 1'!$A$2:$A$7&"|"&'Tab 1'!$C$2:$C$7,'Tab 1'!$A$2:$A$7&"|"&'Tab 1'!$C$2:$C$7,0)=ROW('Tab 1'!$A$2:$A$7)-ROW('Tab 1'!$A$2)+1),--(MONTH('Tab 1'!$D$2:$D$7)=COLUMNS($B3:B3)),--(YEAR('Tab 1'!$C$2:$C$7)=$B$1))

Hope this helps

M,

 

[Marcelo Branco]

oops...

The second formula should be placed in B4, not in C3

M.

 

[Phil13]

oops...

The second formula should be placed in B4, not in C3

M.

Thnks I will try this tonight and will let you know. This is definitely not a formula I could have come up with myself