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.

<tbody> [TD="width: 112, bgcolor: transparent"] [/TD]
[TD="width: 118, bgcolor: transparent"] A [/TD]
[TD="width: 118, bgcolor: transparent"] B [/TD]
[TD="width: 123, bgcolor: transparent"] C [/TD]
[TD="width: 123, bgcolor: transparent"] D [/TD]
[TD="width: 112, bgcolor: transparent"] E [/TD]
[TD="width: 112, bgcolor: transparent"] 1 [/TD]
[TD="width: 118, bgcolor: transparent"] Project no [/TD]
[TD="width: 118, bgcolor: transparent"] Project title [/TD]
[TD="width: 123, bgcolor: transparent"] Project start date [/TD]
[TD="width: 123, bgcolor: transparent"] Project Finish [/TD]
[TD="width: 112, bgcolor: transparent"] [/TD]
[TD="width: 112, bgcolor: transparent"] 2 [/TD]
[TD="width: 118, bgcolor: transparent"] 01 [/TD]
[TD="width: 118, bgcolor: transparent"] Title 1 [/TD]
[TD="width: 123, bgcolor: transparent"] 01/01/2019 [/TD]
[TD="width: 123, bgcolor: transparent"] 01/05/2019 [/TD]
[TD="width: 112, bgcolor: transparent"] [/TD]
[TD="width: 112, bgcolor: transparent"] 3 [/TD]
[TD="width: 118, bgcolor: transparent"] 01 [/TD]
[TD="width: 118, bgcolor: transparent"] Title 2 [/TD]
[TD="width: 123, bgcolor: transparent"] 01/01/2019 [/TD]
[TD="width: 123, bgcolor: transparent"] 01/05/2019 [/TD]
[TD="width: 112, bgcolor: transparent"] [/TD]
[TD="width: 112, bgcolor: transparent"] 4 [/TD]
[TD="width: 118, bgcolor: transparent"] 01 [/TD]
[TD="width: 118, bgcolor: transparent"] Title 3 [/TD]
[TD="width: 123, bgcolor: transparent"] 01/01/2019 [/TD]
[TD="width: 123, bgcolor: transparent"] 01/05/2019 [/TD]
[TD="width: 112, bgcolor: transparent"] [/TD]
[TD="width: 112, bgcolor: transparent"] 5 [/TD]
[TD="width: 118, bgcolor: transparent"] 02 [/TD]
[TD="width: 118, bgcolor: transparent"] Title 4 [/TD]
[TD="width: 123, bgcolor: transparent"] 01/01/2019 [/TD]
[TD="width: 123, bgcolor: transparent"] 01/06/2019 [/TD]
[TD="width: 112, bgcolor: transparent"] [/TD]
[TD="width: 112, bgcolor: transparent"] 6 [/TD]
[TD="width: 118, bgcolor: transparent"] 03 [/TD]
[TD="width: 118, bgcolor: transparent"] Title 5 [/TD]
[TD="width: 123, bgcolor: transparent"] 01/02/2019 [/TD]
[TD="width: 123, bgcolor: transparent"] 01/09/2019 [/TD]
[TD="width: 112, bgcolor: transparent"] [/TD]
[TD="width: 112, bgcolor: transparent"] 7 [/TD]
[TD="width: 118, bgcolor: transparent"] 03 [/TD]
[TD="width: 118, bgcolor: transparent"] Title 6 [/TD]
[TD="width: 123, bgcolor: transparent"] 01/03/2019 [/TD]
[TD="width: 123, bgcolor: transparent"] 01/08/2019 [/TD]
[TD="width: 112, bgcolor: transparent"] [/TD]
</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:

<tbody> [TD="width: 113, bgcolor: transparent"] [/TD]
[TD="width: 119, bgcolor: transparent"] A [/TD]
[TD="width: 120, bgcolor: transparent"] B [/TD]
[TD="width: 121, bgcolor: transparent"] C [/TD]
[TD="width: 118, bgcolor: transparent"] D [/TD]
[TD="width: 116, bgcolor: transparent"] E [/TD]
[TD="width: 113, bgcolor: transparent"] 1 [/TD]
[TD="width: 119, bgcolor: transparent"] [/TD]
[TD="width: 120, bgcolor: transparent"] January [/TD]
[TD="width: 121, bgcolor: transparent"] February [/TD]
[TD="width: 118, bgcolor: transparent"] March [/TD]
[TD="width: 116, bgcolor: transparent"] April [/TD]
[TD="width: 113, bgcolor: transparent"] 2 [/TD]
[TD="width: 119, bgcolor: transparent"] Project Start [/TD]
[TD="width: 120, bgcolor: transparent"] [/TD]
[TD="width: 121, bgcolor: transparent"] [/TD]
[TD="width: 118, bgcolor: transparent"] [/TD]
[TD="width: 116, bgcolor: transparent"] [/TD]
[TD="width: 113, bgcolor: transparent"] 3 [/TD]
[TD="width: 119, bgcolor: transparent"] Project Finish [/TD]
[TD="width: 120, bgcolor: transparent"] [/TD]
[TD="width: 121, bgcolor: transparent"] [/TD]
[TD="width: 118, bgcolor: transparent"] [/TD]
[TD="width: 116, bgcolor: transparent"] [/TD]
[TD="width: 113, bgcolor: transparent"] 4 [/TD]
[TD="width: 119, bgcolor: transparent"] [/TD]
[TD="width: 120, bgcolor: transparent"] [/TD]
[TD="width: 121, bgcolor: transparent"] [/TD]
[TD="width: 118, bgcolor: transparent"] [/TD]
[TD="width: 116, bgcolor: transparent"] [/TD]
[TD="width: 113, bgcolor: transparent"] 5 [/TD]
[TD="width: 119, bgcolor: transparent"] [/TD]
[TD="width: 120, bgcolor: transparent"] [/TD]
[TD="width: 121, bgcolor: transparent"] [/TD]
[TD="width: 118, bgcolor: transparent"] [/TD]
[TD="width: 116, bgcolor: transparent"] [/TD]
[TD="width: 113, bgcolor: transparent"] 6 [/TD]
[TD="width: 119, bgcolor: transparent"] [/TD]
[TD="width: 120, bgcolor: transparent"] [/TD]
[TD="width: 121, bgcolor: transparent"] [/TD]
[TD="width: 118, bgcolor: transparent"] [/TD]
[TD="width: 116, bgcolor: transparent"] [/TD]
[TD="width: 113, bgcolor: transparent"] 7 [/TD]
[TD="width: 119, bgcolor: transparent"] [/TD]
[TD="width: 120, bgcolor: transparent"] [/TD]
[TD="width: 121, bgcolor: transparent"] [/TD]
[TD="width: 118, bgcolor: transparent"] [/TD]
[TD="width: 116, bgcolor: transparent"] [/TD]
</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?


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

January​

[/td][td]

February​

[/td][td]

March​

[/td][td]

April​

[/td][td]

May​

[/td][td]

June​

[/td][td]

July​

[/td][td]

August​

[/td][td]

September​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Start Date​

[/td][td]

2​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Finish Date​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[/table]

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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD="bgcolor: #C5D9F1"]

Year​

[/TD]
[TD="bgcolor: #C5D9F1"]

2019​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Month​

[/TD]
[TD]

January​

[/TD]
[TD]

February​

[/TD]
[TD]

March​

[/TD]
[TD]

April​

[/TD]
[TD]

May​

[/TD]
[TD]

June​

[/TD]
[TD]

July​

[/TD]
[TD]

August​

[/TD]
[TD]

September​

[/TD]
[TD]

October​

[/TD]
[TD]

November​

[/TD]
[TD]

December​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Start Date​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Finish Date​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
</tbody>[/TABLE]


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