Hi,
I have a table with several columns. Two of these columns (that are not consecutive) are similar to this:
Item Reference
Apple 123456
Apple 456789
Lemon 123456
Lemon 123456
I need a formula (located in a different sheet) that gives me the number of unique references for a particular item. For instance, the answer for Apple would be 2, and the answer for Lemon would be 1.
I've tried several conbinations of Count.if and Count.ifs but no result.
Thanks in advance,
IN C1, put this and copy down.
=SUMPRODUCT(($A$1:A1=A1)*($B$1:B1=B1))
Then use this one to get your result.
=SUMPRODUCT((A1:A4="Apple")*(C1:C4=1))
=SUMPRODUCT((A1:A4="Lemon")*(C1:C4=1))
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
with an pivot table?
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Can't do Pivots either. Only a formula for each particular item on its right.
why can't you do Pivot's ??
Fotis, SumProduct on that operation requires to copy the formula on each line of the table. And it contains thousands of records. It would slow dramatically the sheet.
The number of items is les than hundred, so I need a formula to apply to each of these hundred, on other sheet.
We can use Countifs and no sumproduct. Ii will be much more better.
Something like this.
=COUNTIFS($A$1:A1,A1,$B$1:B1,B1)--Not Tested, because Just now i am in a Laptop using Excel 2003.
Never mind if your result will be in other sheet.
First formula in column C-this will be a helper and hidden if you like column-is important to get your result.
If you like, upload a small sample workbook.
Well, yes, but I would prefer not to use the main sheet for any operation. Your solution implies to add some columns on the main sheet, what I would do if there is not any other alternative.
Hi,
Solution found:
{=SUM(IF(FREQUENCY(IF(A$1:A$4=D1,B$1:B$4),B$1:B$4)>0,1))}
Thanks all for your help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks