Hello i made an atach of 2 shets.
In the first sheet i would like to bring in column b the unique orders by id date and agent. i want the infos from sheet 2 column a, b, c)
Can you help me please?
Can you explain it?not necesearly.
Thank you very much!
Hi,
Can you please fill in manually the answers you expect to see in sheet1?
This will be easier in order to understand what you are looking for.
Thanks.
Normaly i do it by removing duplicates from column a (id),
after this i use pivot on the name of the seller and i obtain their uniq number of orders.
My problem is that
in the main report that i use to do this, i have duplicates by id,
because the report is intentionaly showing me also the number of products,
For my agent performances I only want to see the unic number of orders.
I can simply do it by pivoting But when it s about days and quantitys i want to escape this pivot thing
I saw some 1/countif formulas but i don`t manage to make it work.
Got me now?
Last edited by alexxl; 05-05-2020 at 12:14 PM.
So if we take Alex, for example, you want to count the number of times his name appears on a certain date with unique ID#? (3 times on 03-05-20 and 2 times on 03-05-2020)?
It would easier to understand if you upload your file with the results manually typed in sheet "1".
Thanks.
Please try at
B2
=COUNT(1/FREQUENCY(IF('2'!$C$2:$C$44=A2,'2'!$A$2:$A$44),'2'!$A$2:$A$44))
C2
=COUNT(1/FREQUENCY(IF('2'!$C$2:$C$44=A2,IF('2'!$B$2:$B$44=C$1,'2'!$A$2:$A$44)),'2'!$A$2:$A$44))
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Than you very much. First i Tryed with something like =SUM(IF(A2='2'!C:C,1/(COUNTIFS('2'!C:C,'1'!A2,'2'!A:A,'2'!A:A)),0))
=SUM(IF(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATE(2016, 9, 30)*($D$2:$D$20>=DATE(2016, 9, 1))), 1/COUNTIFS($C$2:$C$20, "Tom", $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, "<="&DATE(2016, 9, 30),$D$2:$D$20, ">="&DATE(2016, 9, 1))), 0)
But i don`t want to search beneth those dates.
I like to use my formulas templates with refferences to cells i think is much easier.
Thank you very much for your solution.
Appreciate it.
Thank you very much for your answer also.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks