LinkBack URL
About LinkBacks
Hello, I need a Help
I have a Selling out data with Sales Name, Item Name, Shop name
I want to count how many shop under specific sales name who buy specific item name in specific month.
please see Attachment.
thx for your help
Hello, I need a Help
I have a Selling out data with Sales Name, Item Name, Shop name
I want to count how many shop under specific sales name who buy specific item name in specific month.
please see Attachment.
thx for your help
Last edited by ZANDRY10085; 03-23-2020 at 10:00 PM.
try this in G6 and drag down
Minor Edit to formula from my original post
Please Login or Register to view this content.
Last edited by Crooza; 03-23-2020 at 01:46 AM.
Happy with my advice? Click on the * reputation button below
i want to count the number of shop who buy that item (same name shop count as 1) not the total item, but thx for your reply
If you want the count then use this as an array formula
Enter + control + Shift then drag down
Please Login or Register to view this content.
sry if my word confuse you, your formula give different result with what i want.
what i want is
name item shop qty
vian cylinder aa 5
vian cylinder ab 0
vian cylinder ac 7
the result will 2
vian cylinder aa 5
vian cylinder aa 4
vian cylinder ab 5
vian cylinder ac 2
the result will 3
Sheet2
C2=SUM(IF(FREQUENCY(IF(Sheet1!B2:B100=Sheet2!B2,IF(Sheet1!A2:A100=Sheet2!A2,IF(INDEX(Sheet1!D2:O100,0, MATCH(Sheet2!C1,Sheet1!D1:O1,0))<>0,MATCH(Sheet1!C2:C100,Sheet1!C2:C100,0)))),ROW(Sheet1!C2:C100)-ROW(Sheet1!C2)+1),1))
Control+shift+enter
Thx for your reply, but your formula result in my worksheet = #N/A or #VALUE!
EDITED
Vian + Battery + all month your formula give result = 1
Other combination your formula give result = #Value!
idk, but looks like something not right in this part "MATCH(Sheet1!C2:C100,Sheet1!C2:C100,0)"
Last edited by ZANDRY10085; 03-23-2020 at 09:39 PM.
i got this formula, but this formula only count how many unique shop who buy some item. dont know how to combine it with sales name, item name and specific month.
D= qty, C= Shop name
=SUMPRODUCT(($D$2:$D$20<>0)/COUNTIFS($C$2:$C$20;$C$2:$C$20&""))
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however the thread title does not really convey what your request is about.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
sry, i make a mistake.
your formula @CARACALLA work fine
SUM(IF(FREQUENCY(IF(Sheet1!B2:B100=Sheet2!B2,IF(Sheet1!A2:A100=Sheet2!A2,IF(INDEX(Sheet1!D2:O100,0, MATCH(Sheet2!C1,Sheet1!D1:O1,0))<>0,MATCH(Sheet1!C2:C100,Sheet1!C2:C100,0)))),ROW(Sheet1!C2:C100)-ROW(Sheet1!C2)+1),1))
thx for your help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks