Hi,
As per the sample data attached, I need to count unique "Used Car" by "Jagadheesh" and "Asish" each day.
Hope you understand Please see the data in sample attached.
Thanks you
Hi,
As per the sample data attached, I need to count unique "Used Car" by "Jagadheesh" and "Asish" each day.
Hope you understand Please see the data in sample attached.
Thanks you
Try this:
=SUMPRODUCT((1/(COUNTIF(Sheet1!$D$2:$D$518,Sheet1!$D$2:$D$518))*(Sheet1!$B$2:$B$518="Jagadeesh")*(Sheet1!$C$2:$C$518=$B3)))
and this:
=SUMPRODUCT((1/(COUNTIF(Sheet1!$D$2:$D$518,Sheet1!$D$2:$D$518))*(Sheet1!$B$2:$B$518="Asish")*(Sheet1!$C$2:$C$518=$B3)))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi AliGW,
I have tried the formula, it's working for 1st date i.e. 01-May-2021. For rest all dates not giving correct result. Showing in Decimals.
Please check the attached sample data.
Please suggest further.
Thank you
Try this ordinary (NOT an array) formula:
=SUM(INDEX((Sheet1!$C$2:$C$518=$B3)*(Sheet1!$B$2:$B$518=C$2)/COUNTIFS(Sheet1!$C$2:$C$518,Sheet1!$C$2:$C$518&"",Sheet1!$B$2:$B$518,Sheet1!$B$2:$B$518&"",Sheet1!$D$2:$D$518,Sheet1!$D$2:$D$518&""),0))
Last edited by Glenn Kennedy; 06-19-2021 at 03:09 AM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Sheet2
C3=SUM(IF(FREQUENCY(IF(Sheet1!$C$2:$C$518=Sheet2!$B3,IF(Sheet1!$B$2:$B$518=TRIM(SUBSTITUTE(Sheet2!C$2,"Count of","")),MATCH(Sheet1!$D$2:$D$518,Sheet1!$D$2:$D$518,0))),ROW(Sheet1!$B$2:$B$518)-ROW(Sheet1!$B$2)+1),1))
CONTROL+SHIFT+ENTER
Copy across and down
worksheet name : sheet2
cell C3 array formula , Drag down and across
HTML Code:
Last edited by wk9128; 06-19-2021 at 06:10 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks