Counting unique text values based on month

jimmy501

New Member
Joined
Jul 1, 2015
Messages
2
I'm exporting data from Salesforce monthly in order to summarise our sales pipeline.

The data come out as:
Account Name (A)Contract end date (C)
Company A20/05/2016
Company D23/11/2015
Company C17/09/2015
Company A31/01/2018
Company B31/01/2018
Company B03/05/2018
Company A31/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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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:
Rich (BB code):

=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))
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top