• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get count of invoice based on condition

Junarkar

Member
Hi,

I'm trying to create a report based on few conditions. I have excel file with 3 sheets - Promo Master, Sales Data and Report. What I want is the count of invoices in which the staff sold a particular product which is in promo master.

Example;
Promo Master;
SKU
221022​
220808​
218882​

Sales data;
Billing DateSales Person IDInvoiceArticle No
01-03-2020​
115000​
SLA123456000
205143​
01-03-2020​
115000​
SLA123456001
219133​
01-03-2020​
16123​
SLA123456002
221022
01-03-2020​
16123​
SLA123456002
224029​
01-03-2020​
137778​
SLA123456004
219836​
01-03-2020​
16123​
SLA123456005
168541​
01-03-2020​
16123​
SLA123456006
224029​
01-03-2020​
16123​
SLA123456007
200898​
01-03-2020​
SLA123456008
221022

Report Format;
DateEmp CodeEmp NameCount of Total No. of Inv.Count of Promo sale inv.
01-03-2020​
16123​
Ram
4​
2​
02-03-2020​
03-03-2020​
04-03-2020​
05-03-2020​


In this you can see that employee 16123 has made couple of invoices out of which two invoices contain article code which is present in Promo master (221022).
Also note that one invoice can have multiple articles (invoice no. SLA123456002 in this example which have two articles in it). This should be counted as one.

Please give some idea.

Sample file is attached.

Regards
Junarkar
 

Attachments

Peter Bartholomew

Well-Known Member
Firstly, I would convert all tabular data to Excel Tables. That is a personal preference that allows me to understand formulas.
Given that, I suggest you need a helper column on the sales table to indicate whether the item is on promotion or not.
= COUNTIFS( Promo[SKU], [@[Article No]] )
This avoids the need for repeated searches of the Promo table,

I do not agree with your counts but the formula could be
= COUNTIFS( Sales[Sales Person ID], [@[Emp Code]], Sales[Promo], ">0", Sales[Billing Date], [@Date] )
 

Attachments

p45cal

Well-Known Member
As with Peter's response I've added a column to your Sales Data table and made it into an Excel table.
I've also added a Sales Person ID in cell D10 and populated some names in Sales Person Name column.
Bearing the above in mind, is the table at A10 of the Report sheet showing the correct data?
 

Attachments

Junarkar

Member
Firstly, I would convert all tabular data to Excel Tables. That is a personal preference that allows me to understand formulas.
Given that, I suggest you need a helper column on the sales table to indicate whether the item is on promotion or not.
= COUNTIFS( Promo[SKU], [@[Article No]] )
This avoids the need for repeated searches of the Promo table,

I do not agree with your counts but the formula could be
= COUNTIFS( Sales[Sales Person ID], [@[Emp Code]], Sales[Promo], ">0", Sales[Billing Date], [@Date] )
First sorry for the late replay. and Thanks Peters, It worked....
 

Junarkar

Member
As with Peter's response I've added a column to your Sales Data table and made it into an Excel table.
I've also added a Sales Person ID in cell D10 and populated some names in Sales Person Name column.
Bearing the above in mind, is the table at A10 of the Report sheet showing the correct data?
Sorry the delay in replay.. Yes that worked.... Thanks alot for the solution.
 
Top