Find Out Count of Specific Product's Invoice By Sales Representatives

[miltonsaha9200]

Suppose I have a Sheet, where i need to find out Specific count of product's invoice by Sales Representatives. I have attached the excel file for your reference.

I need to find out how how many invoices are there where the product Example: (PCNO 100ML BICOL NMRP) has quantity over 6 Pieces but by Sales Representatives.

New Microsoft Excel Worksheet.xlsx

[BadlySpelledBuoy]

There's no sample file attached.

BSB

[miltonsaha9200]

Added Now. Please check

[AliGW]

Are you still using Excel 2016, or somthing newer?

[miltonsaha9200]

Yes. Excel 2016 with office 365 subscription. Actually it's a work laptop so cant change anything

[AliGW]

That's not Excel 2016, it's Excel 365 - please update your profile.

Regarding your PM:

Hi there, I have an excel problem which just now posted on forum. But i was wondering if you could join me in a zooms or teams call

Please do NOT do this. All help offered is to be offered HERE in your thread so that it benefits the entire community and not just you. Please do NOT approach members in this way, and in any case, we have Forum General Guideline #3, which you should read.

Try this in F2 of Sheet1:

=CHOOSECOLS(SORT(FILTER('RAW DATA'!A2:M212,'RAW DATA'!H2:H212>=6),,1),1,6,13,8)

It pulls through all items with quantities bigger than or equal to 6 and sorts them by rep.

[miltonsaha9200]

nope it didn't serve my purpose.

Let me clarify the whole concept:

If one Sales Representative makes an invoice of Product: "PCNO 100ML BICOL NMRP" more than 6 QTY (Quantity in Piece) then it will be counted as 1 EC & I need to figure out all ECs. in this way various products has various conditions, some has to be 24 QTY or 12 QTY. I need the base formula, then need to modify those by particular QTY rules.



& Regarding the PM, as it's a but critical to understand by typing thought that'd be a good solution.

[AliGW]

Well, a 'thank you' for starters might have been nice!

In your workbook, your 'expected' results show values of 6 - that is not MORE THAN 6.

Provide a SMALLER sample dataset (around 50 rows) with EXPECTED RESULTS manually mocked up.

EDIT: You need to update your forum profile NOW, please.

[miltonsaha9200]

Sorry. Thank You for the prompt & detailed response. Here's the shorter file size with conditions & result i need.

[AliGW]

Please remove 16 from your profile - the subscription product is 365.

I shall have another look. Please STOP the unnecessary quoting - it's just clutter.

[AliGW]

There are no expected reults:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	F	G
2	SR NAME	TOTAL EC
3	Sajal Kanti Das
4	Deloar
5	Md Sajjad Hossain
6	Md Monjur Ahmed
7	Rasel Ahmed Rana
8	PSR(Sajol Kumar)

Sheet: Sheet1

Please fill in at least two or three and repost the workbook.

The conditions table was never mentioned in your opening post - is that, too, to be included?

You seem to have shifted the goalposts already ... Is there anything else we need to know?

[AliGW]

If these are the correct results:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	F	G
2	SR NAME	TOTAL EC
3	Sajal Kanti Das	22
4	Deloar	15
5	Md Sajjad Hossain	19
6	Md Monjur Ahmed	18
7	Rasel Ahmed Rana	12
8	PSR(Sajol Kumar)	4

Sheet: Sheet1

then this in G3 copied down;

=COUNT(FILTER(LET(d,$C$2:$D$91,MAP(INDEX(d,,1),INDEX(d,,2),LAMBDA(x,y,IF(y>=--MID(VLOOKUP(x,$I$3:$J$26,2,0),3,5),y,0)))),$A$2:$A$91=F3))

If not, then no more guesses from me.

[AliGW]

Another guess. If this is the expected result:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	F	G
2	SR NAME	TOTAL EC
3	Sajal Kanti Das	19
4	Deloar	15
5	Md Sajjad Hossain	13
6	Md Monjur Ahmed	16
7	Rasel Ahmed Rana	12
8	PSR(Sajol Kumar)	4

Sheet: Sheet1

then this in G3 copied down:

=LET(d,$C$2:$D$91,m,MAP(INDEX(d,,1),INDEX(d,,2),LAMBDA(x,y,IF(y>=--MID(VLOOKUP(x,$I$3:$J$26,2,0),3,5),VLOOKUP(x,$I$3:$KJ$26,3,0),0))),SUM(FILTER(m,$A$2:$A$91=F3)))

[miltonsaha9200]

Okay Thank You So Much. Let me try then providing feedback

[HansDouwe]

Here is another solution for the whole column in one go:

Please empty all cells below G3 and try in G3:

Please Login or Register  to view this content.

[miltonsaha9200]

Thank you so much for putting that much effort. But that's not correct again. Please see in your 8th row named "PSR(Sajol Kumar)" result's 4, but look here in his raw data his unique invoice is one, or similar invoice is in 4 & most importantly here also have 2 ITEM NAME, not 4, so the result is supposed to be 2.

Attachment 846742

[miltonsaha9200]

Here is another solution for the whole column in one go:

Please empty all cells below G3 and try in G3:

Please Login or Register  to view this content.

Thank you so much for putting that much effort. But that's not correct again. Please see in your 8th row named "PSR(Sajol Kumar)" result's 4, but look here in his raw data his unique invoice is one, or similar invoice is in 4 & most importantly here also have 2 ITEM NAME, not 4, so the result is supposed to be 2.

Attachment 846742

[AliGW]

Until you provide a workbook with your expected results included, I shall do no more. You are assuming that I can see WHY it's wrong, but I have no clue, as you have failed so far to explain properly what it is you are trying to achieve.

I have made two guesses so far - I shall waste no more of my time. If you provide a suitable sample workbook and a FULL explanation, I shall have another look.

[miltonsaha9200]

EXCEL 2 V2.xlsx

Please find the one last attached file. I have put the manual results as well. Sorry for my weaker communication.

One last time I'm explaining fully. If this fails I'll give up.


The condition is , here i need to check how much marks each sales representative's has got. Here are 6 srs. I need to check in any single memo how many item's has invoice qtys.

Suppose, here the first invoice no. BILL230008869 has 2 items, and if those item's qty matches with the condition he'll get 1 point i.E. Ec for the single items. So here in this invoice no. BILL230008869 all of the conditions matches with requirements which is "equal to or greater than 2", so he gets 2 marks(ec) for that single bill.

I need this result as i mentioned by sales representatives & by unique invoice no.

[AliGW]

Please do not SHOUT at us - we are not idiots.

Please replace the ALL CAPS section with sentence case, as it is difficult to read.

One last time I'm explaining fully. If this fails I'll give up.

This is the first time you have explained fully! I might have a look shortly if I have time.

[miltonsaha9200]

I'm not shouting, I'm just pissed off at myself for not clarifying property. Btw thanks to Your prompt responses. Please check at your convenient time.

[AliGW]

Thank you. For your information, if you type sentences in ALL CAPS online, it is considered as shouting.

I have run out of time now - I shall try to have a look later on, but I am struggling to understand the explanation, I have to say.

[Glenn Kennedy]

Hi there! I >>>think<<< this covers all your requirement. However I think you made a mistake in your answer for Rasel Ahmed Rana - maybe you didn't see the entry at row 38.

Please check!!

Please Login or Register  to view this content.

Is the traffic in Dhaka still as crazy as ever? I think it has the WORST traffic jams I have ever seen... I worked there 4 or 5 times, about 10-15 years ago.

[Glenn Kennedy]

I forgot to add, I removed the >= from column I. They just turn useful numbers into text.

[HansDouwe]

This formla returns the whole table and counts pro unique invoice pro unique item:

Please Login or Register  to view this content.

This formula counts 4 for Rasel Ahmed Rana conform epected results (and also conform my expected answer)
And this formula counts 4 also for Md Sajjad Hossain this is 1 less conform your expected result, but it is conform my expected result.

See attachment for the expected results (inclusive an explanation) and Glenn's formula and my formula

[Glenn Kennedy]

Well spotted... and easily corrected:

Please Login or Register  to view this content.

[/FORMULA]

[miltonsaha9200]

Thank You So Much Guys. I think It'll solve my problem. I haven’t tested fully yet but surely will give feedback.

[miltonsaha9200]

Yes Glenn, Dhaka's traffic hasn’t changed at all. Though we've inaugurated Metro Rails, Elevated Expressway etc. for reducing the traffic. Great to know that.

[miltonsaha9200]

Thank you guys. I think it has worked. But somehow still has slight problem.

As per Glenn's updated last formula the cumulative result comes 12131 but the result would be 12154, which is slightly different from the actual result. However I had no choice but to upload the whole file once again as i think it only needs slight modifications to somewhere.

And I don't know why, when i put Hans formula on my main raw sheet it doesn't work. Strange

EXCEL LATEST V3(1).xlsx

I Know it's too much to ask for but trust me guys I'm stuck badly.

[HansDouwe]

when i put Hans formula on my main raw sheet it doesn't work

My formula was not created efficiently enough for a large file and needs to be reorganized.
I have already done some testing and the formula will return exactly the same results as Glenn's formula after reorganization.

I can't explain your expected results that are different.

[Glenn Kennedy]

Manual checking of 12,000++ rows to find an unspecified error is far beyond what I consider a reasonable request for help.

So, I'm not going to do it!!

How do you KNOW that the expected answer is correct? Can you identify (with 100% certainty) any ONE name (preferably one NOT from one with EC totals >100) where the delivered answer is incorrect.

Ypu'll need to put more effort in at your end before I do at mine.

[Glenn Kennedy]

I have done a manual check on Md Delwar Husen. You can see what I did on the right. I could find no discrepancies. So, over to you.

One Q. I see that all of the EC values in this sheet are 1. In the sample that I worked on, there was at least one row with an EC value of 2. Is its absence intentional? if they're all 1... why have the EC column at all?

[miltonsaha9200]

Thank You So Much for putting that much effort. The expected results are being provided from our company, I don't know from where they extract that or get that. However as you've checked it manually and sticking on the previous formula i'll take that <3

Thank You So Much

[miltonsaha9200]

Thank You Hans. I'm sticking to Glenn's formula so it's always a <3

[Glenn Kennedy]

I only looked at ONE name to see if I could find a discrepancy. Not ALL of them. YOU can do the rest.