This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi, I'm new to this community, I'm usually at the power apps one which is very helpful I hope I can find a similar experience on this one as well.
I deal with huge datasets that are generated every day and for one of the tasks i have to manually cross examine data within different datasets and is very tedious process that I believe would be automated some with scripts/macros but after a few days trying different things out I decided to to give the forum a try. For your understanding I uploaded an image (since I know I'm limited trying to explain with words with excel terms). I apologize but for some reason the image upload are failing for me and I couldn't find a proper solution online so I had to host the image instead:
https://tinypic.host/i/excel.i7FNp
So when you look at the image you will see there is a table with five columns an a pivot table to the right. What I want to achieve with the table at the left is that in the "MyFormula" column I could, with a formula, achieve a result similar to the result showing on the "Expectedvalue" column (that I manually inserted the values so you can see what I want). Now the values I want are supposed to be the distinct values of the fruits ("Fruit" column) specifically for the range of the specific retailer ("Retailer" column). For example, since the Retailer 1 has 4 different kind of fruits the expected value for that retailer should be 4 on the Myformula column. Yet the closest Ive managed is to count the unique values for fruit for that specific retailer using the formula [=COUNTIFS(A:A,A2,B:B,B2)] and draggin it down hence the current result you can see in the MyFormula Column.
I've included the pivot table that is the current way we do data work right now but is limited for automation because for me to get the data I have to manually click on the Distinct Count of Fruit value so excel makes a whole new sheet and show me the specific values but that complicates the effort for me to be able to automate things with my current knowledge (which is not much) of excel. I hope I made some sense and sorry for the long explanation. Thank you.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Hi,
In cell E2, enter this formula and drag down
=counta(unique(filter($B$2:$B$20,$A$2:$A$20=A2)))
Hope this helps.
Answer accepted by question author
Sorry, it wasn't clear that H:I was a pivot table.
Try this file.
Thank you that solved it!
In I2:
=COUNTA(UNIQUE(FILTER(B:B,A:A=H2)))
copied down.
Maybe i didn't understood the formula but that only works for the Retail1 and requires the pivot data which is not available by default, regardless if you find a way using references with the pivot table i can just build it each time. Thank you for replying. To clarify when i use your formula and drag it down on "Myformula" it only gives the correct (expected) value for the retailer 1 because is referencing only the retailer 1 in the pivot.
In I2:
=COUNTA(UNIQUE(FILTER(B:B,A:A=H2)))
copied down.