I'm exporting data from Salesforce monthly in order to summarise our sales pipeline.
The data come out as:
<tbody>
</tbody>
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
The data come out as:
Account Name (A) | Contract end date (C) |
Company A | 20/05/2016 |
Company D | 23/11/2015 |
Company C | 17/09/2015 |
Company A | 31/01/2018 |
Company B | 31/01/2018 |
Company B | 03/05/2018 |
Company A | 31/09/2015 |
<tbody>
</tbody>
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