Countif with Indirect formula help

Dear Experts, I got stuck once again and looking for your precious support. I have tried with countif formula but not succeeded. Please find here attached sample workbook where i have mentioned the expected results for a sales person monthwise.

I do have a situation where i need to count the numbers of the customers Newly added for the month and how many out of them got removed in the month from their sales pipeline.

For Jan month if the Customer name is disappeared from Feb then that would be counted as 1Qty that will be appeared in cell B3 Report tab under customer removed from the pipeline and that particular removed customer deal value will be appeared in Cell C3 Report tab.

I am looking my expected results in Report tab from Cell B2:C4 based on the Selected Sales Person from the dropdown and the output will be coming from Master tab.

For eg. Sales person ABC in the Master tab there are only 3 customer have been removed from Feb-18 which was added in Jan-18 so the Qty for Removed customer would be 3 in cell B3 Report tab and deal value for those 3 customers which got removed from Feb-18 pipeline will be appeared in cell C3 Report tab and the same criteria would also be applicable for the upcoming months.

I hope i have explained everything what i am looking for. Please feel free to revert back in case of further clarification.

Many thanks and kind regards,

Neilesh


Dear Experts i do apologies for making the changes in the expected results and accordingly i have mentioned above now everything finally my expectations. Please find here attached revised sample workbook.

Once again apologies

Please be patient.

Dear Experts, The essence of the expected result is, count if the customer name is appearing only once from the period they appeared in the Master tab and for which Sales person.

For eg. For Sales Person ABC if the Customer name is appearing for Jan month and if it is not getting repeat after the Jan month would be consider as Account Removed from Sales pipeline and if Customer is getting repeat in the upcoming month from the month once the customer appeared then that customer wont cover under Account Removed.

Dear Experts, I know the getting expected results is quite difficult for me but i am very much sure you guys can crack it. Please find here attached more simplified workbook for the expected results. Now i have removed months tab and combined all the months in a new Master tab. Is it possible now to get the expected results.

Criteria for New Accounts Added to the Sales Pipeline (For Cell E2 & F2 Report Tab):

Total Quantity added In (immediate upcoming month from Jan) Feb-18 as New Customers. These Customers added in Feb-18 are not available in Jan-18 in the Master tab. That is the reason those are falling under New Customer Added. So the New customers added in Quantity are total 3 in Feb-18 will be appeared in Cell E2 Report tab and their Total Contract Value and Service Revenue will be appeared in Cell F2 in Report tab.

New Customers added in Feb-18 are highlighted with Yellow Color.


Criteria for Accounts Removed from the Sales Pipeline (For Cell J2 & K2 Report Tab):

Total Quantity of those Customer which were available in Jan-18 but disappeared in Feb-18. So the total Quantity of disappeared Customers in Feb-18 Sales Pipeline are 6 (Highlighted with Blue Color). Quantity 06 will be appeared in Cell J2 in Report tab and their Total Contract Value and Services Revenue will be appeared in Cell K2 in Report tab.

Request to you please help me. Any help on the same would highly be appreciated.

Many Thanks and Regards,

Neilesh

Dear Neilesh Kumar

The solution I propose have a two helper columns in Master Sheet
Column AL from AL2

Formula:

=COUNTIFS($A$2:$A$22,IF(Working!$A$2="ALL","<>",Working!$A$2),$C$2:$C$22,C2,$B$2:$B$22,DATE(YEAR(B2),MONTH(B2)-1,1))

Column AM from AM2

Formula:

=COUNTIFS($A$2:$A$22,IF(Working!$A$2="ALL","<>",Working!$A$2),$C$2:$C$22,C2,$B$2:$B$22,DATE(YEAR(B2),MONTH(B2)+1,1))

Use in E2, F2, J2 and K2 the folowing formulas (and copy down)

Formula:

=COUNTIFS(Master!$AL$2:$AL$22,0,Master!$B$2:$B$22,Report!$A3)

Formula:

=SUMIFS(Master!$L$2:$L$22,Master!$AL$2:$AL$22,0,Master!$B$2:$B$22,Report!$A3)

+SUMIFS(Master!$M$2:$M$22,Master!$AL$2:$AL$22,0,Master!$B$2:$B$22,Report!$A3)

Formula:

=COUNTIFS(Master!$AM$2:$AM$22,0,Master!$B$2:$B$22,Report!$A2)

Formula:

=SUMIFS(Master!$L$2:$L$22,Master!$AM$2:$AM$22,0,Master!$B$2:$B$22,Report!$A2)

+SUMIFS(Master!$M$2:$M$22,Master!$AM$2:$AM$22,0,Master!$B$2:$B$22,Report!$A2)

See the file

You can use the array formulas with no helper columns - sheet Report
E2

=COUNT(IF((Master!A$2:A$22=V$2)*(Master!B$2:B$22<>"")*(Master!C$2:C$22<>""),1/ISNA(MATCH(IF(MONTH(Master!B$2:B$22)=MONTH(A2)+1,Master!C$2:C$22),IF(MONTH(Master!B$2:B$22)=MONTH(A2),Master!C$2:C$22),0))))

F2

=SUM(IF((Master!A$2:A$22=V$2)*(Master!B$2:B$22<>"")*(Master!C$2:C$22<>""),(Master!L$2:L$22+Master!M$2:M$22)*ISNA(MATCH(IF(MONTH(Master!B$2:B$22)=MONTH(A2)+1,Master!C$2:C$22),IF(MONTH(Master!B$2:B$22)=MONTH(A2),Master!C$2:C$22),0))))

J2

=COUNT(IF((Master!A$2:A$22=V$2)*(Master!B$2:B$22<>"")*(Master!C$2:C$22<>""),1/ISNA(MATCH(IF(MONTH(Master!B$2:B$22)=MONTH(A2),Master!C$2:C$22),IF(MONTH(Master!B$2:B$22)=MONTH(A2)+1,Master!C$2:C$22),0))))

K2

=SUM(IF((Master!A$2:A$22=V$2)*(Master!B$2:B$22<>"")*(Master!C$2:C$22<>""),(Master!L$2:L$22+Master!M$2:M$22)*ISNA(MATCH(IF(MONTH(Master!B$2:B$22)=MONTH(A2),Master!C$2:C$22),IF(MONTH(Master!B$2:B$22)=MONTH(A2)+1,Master!C$2:C$22),0))))

Entered with Ctrl+Shift+Enter

**
************