Counting unique text values based on month

[jimmy501]

I'm exporting data from Salesforce monthly in order to summarise our sales pipeline.

The data come out as:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Account Name (A)[/TD]
[TD]Contract end date (C)[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]20/05/2016[/TD]
[/TR]
[TR]
[TD]Company D[/TD]
[TD]23/11/2015[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]17/09/2015[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]31/01/2018[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]31/01/2018[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]03/05/2018[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]31/09/2015[/TD]
[/TR]
</tbody>[/TABLE]













Sheet: Renewal Supplies Forecast

In another Sheet (Renewal Pipeline) I want to record the number of companies with contract end dates in each month, and I don't want to count blank values

I have the year fixed in D2 and the months numbered (D6, D7, D8 etc) starting June 2015 as 6.

The formula I have tried is:

{=SUM(IF(COUNTIF('Renewal Supplies Forecast'!$A$2:$A$8000,'Renewal Supplies Forecast'!$A$2:$A$8000)=0, "", 1/COUNTIF('Renewal Supplies Forecast'!$A$2:$A$8000,'Renewal Supplies Forecast'!$A$2:$A$8000)*((MONTH('Renewal Supplies Forecast'!$C$2:$C$100000)=D$6)*(YEAR('Renewal Supplies Forecast'!$C$2:$C$100000)=$D$2))))}

I keep getting #N/A

Help please

 

[AlexandraT]

A pivot might be your best bet. Have you tried a pivot table?

Alex

 

[Marcelo Branco]

Try this array formula

=SUM(IF(FREQUENCY(IF('Renewal Supplies Forecast'!$A$2:$A$8000<>"",IF(YEAR('Renewal Supplies Forecast'!$C$2:$C$8000)=D$2,IF(MONTH('Renewal Supplies Forecast'!$C$2:$C$8000)=D6,MATCH('Renewal Supplies Forecast'!$A$2:$A$8000,'Renewal Supplies Forecast'!$A$2:$A$8000,0)))),ROW('Renewal Supplies Forecast'!$A$2:$A$8000)-ROW('Renewal Supplies Forecast'!$A$2)+1),1))

confirmed with Ctrl+Shift+Enter

Hope this helps

M.

 

[Aladin Akyurek]

Renewal Line

D2 houses a year of interest like 2015.

D6 houses a month of interest like 1.

E6, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(AccountRange<>"",IF(ISNUMBER(DateRange),
    IF(TEXT(DateRange,"mm-yyyy")=TEXT($D6,"00")&"-"&$D$2,
    MATCH(AccountRange,AccountRange,0)))),
    ROW(AccountRange)-ROW(INDEX(AccountRange,1,1))+1),1))

 

[jimmy501]

Thank you, that worked!