Tricky problem in countif

[sam singha]

Hey Friends,

One more time i am seeking your help. I am fortunate we have your support.
I have sample data sheet. I want to know the count of new customers in each month. These will be the ones who have not ordered in the previous months.
Also, what is the sum of their revenues?
I have tried sumif along with countif and vlook up but no good so far :|
Can you please see if something can be worked out?

THanks in advance.

 

[John Jairo V]

Hi @sam singha.

Please check the file. The formulas are array formulas, this type of formulas must be introduced with the keys Ctrl + Shift + Enter, and not only Enter.

Please comment!
Blessings!

 

[Mike86]

This is similar to what John has. I put a drop down on the Month name so you can select different months.

 

[Somendra Misra]

Hi Sam,

Just another option.

Regards,

 

[NARAYANK991]

Hi @sam singha.

Please check the file. The formulas are array formulas, this type of formulas must be introduced with the keys Ctrl + Shift + Enter, and not only Enter.

Please comment!
Blessings!

Hi John ,

Is it possible we can use the following instead of the ROWS function ?

=SUM(--ISERROR(MATCH(IF(MONTH(Table1[Order date])=MONTH(F5&0),Table1[customer name]),IF(MONTH(Table1[Order date])=MONTH(F5&0)-1,Table1[customer name]),)))

Narayan

 

[NARAYANK991]

This is similar to what John has. I put a drop down on the Month name so you can select different months.

Hi Mike ,

Is it possible we can use the following construct instead of the ROW function , in the helper column ?

=IF(MATCH([@[customer name]],[customer name],0)=ROW([@[customer name]])-ROW(Table1[[#Headers],[customer name]]),"New","Existing")

Narayan

 

[sam singha]

Hi @sam singha.

Please check the file. The formulas are array formulas, this type of formulas must be introduced with the keys Ctrl + Shift + Enter, and not only Enter.

Please comment!
Blessings!

Thank a ton!! You guys are awesome. It will take some time for me to comprehend the solution you gave. But it will get the wheels turning. Thank you!

 

[sam singha]

This is similar to what John has. I put a drop down on the Month name so you can select different months.

Thank you Mike!

 

[sam singha]

Hi Sam,

Just another option.

Regards,

Thank a lot SM!