PDA

View Full Version : Solved: count of different products



danovkos
02-15-2010, 02:22 AM
Hi all,
pls. how can i check how many diferent products have one ID
Wha it mean.
I have ID numbers in column A and in the same row in column B is product.
Result will be in column C.

My table seem something like this and in column C i need result:
ID product result
777 apple 3
777 table 3
777 box 3
555 box 1
666 box 2
666 apple 2
545 pen 1
545 pen 1

thx for help

Bob Phillips
02-15-2010, 03:51 AM
Try this array formula

=FREQUENCY(IF($A$2:$A$9=$A2,IF($B$2:$B$9<>"",1)),IF($A$2:$A$9=$A2,IF($B$2:$B$9<>"",1)))

Aussiebear
02-15-2010, 04:09 AM
Could a sum product equation have worked here Bob? Providing it would recognise Column A data as Text?

danovkos
02-15-2010, 04:27 AM
Try this array formula

=FREQUENCY(IF($A$2:$A$9=$A2,IF($B$2:$B$9<>"",1)),IF($A$2:$A$9=$A2,IF($B$2:$B$9<>"",1)))

i tried it, but it return only 1 :(
did i something wrong?

Aussiebear
02-15-2010, 04:41 AM
Did you copy and paste the formula?

Bob Phillips
02-15-2010, 05:08 AM
Did you array enter it, Ctrl-Shift-Enter?

Bob Phillips
02-15-2010, 05:18 AM
Could a sum product equation have worked here Bob? Providing it would recognise Column A data as Text?

Can't thinks of one of-hand Ted, although text is not the problem.

Aussiebear
02-15-2010, 05:47 AM
Was it on your site where the example used was regarding makes and models of cars in a sumproduct formula

danovkos
02-15-2010, 05:49 AM
thx
of course, i dont use shift +ctrl +enter :(
sorry
but i tried it and now it return count of rows, where is the same ID, not count of diferent products.
Here is sample.

danovkos
02-15-2010, 11:53 PM
nobody know, how can i do that?
:dunno
in my first post is example, but most important in this example are last rows

545 pen 1
545 pen 1

2 ids but only 1 product (pen), so result is 1
this formula return 2

Aussiebear
02-17-2010, 02:01 AM
Try this one in E2 and copy down

=(Sumproduct(($A$2:$A$26=A2)*($B$2:$B$26=B2))

danovkos
02-17-2010, 07:50 AM
i tried it but, this formula counts only how many product has ID
e.g.
id 4 has - dan, dan, blue, dark
it return dan - 2
blue

danovkos
02-17-2010, 07:52 AM
i tried it but, this formula counts only how many product has ID for each product separately.
e.g.
id 4 has - dan, dan, blue, dark
it return dan - 2
blue - 1
dark 1

but i need count how many diferent product has one ID.
In this case is it dan, blue, dark - result will be 3.
So in each row by ID 4 will result 3.

I know, that my explanation is not very cleare. Sorry for that :(
and thx for try

SamT
02-17-2010, 12:33 PM
In column C, Row n

=COUNTIF($A:$A,An)

Where n is the starting Row of your list.
NOT an array formula.

Fill down to the bottom of your list.

Will work even if Column A is not sorted, has blanks, or whatever.

If the list is short: =COUNTA($A[start]:$A(end),A[start])

Bob Phillips
02-17-2010, 12:46 PM
Here is a way.

In D2 enter,
=1/SUMPRODUCT(--($A$2:$A$20=A2),--($B$2:$B$20=B2)
and copy down

and in C2, enter
=SUMIF(A:A,A2,D:D)
and copy down

danovkos
02-18-2010, 12:26 AM
Here is a way.

In D2 enter,
=1/SUMPRODUCT(--($A$2:$A$20=A2),--($B$2:$B$20=B2)
and copy down

and in C2, enter
=SUMIF(A:A,A2,D:D)
and copy down

yes, XLD, this works.
thank you very much for this

Aussiebear
02-18-2010, 12:49 AM
Good to see that we arrived at a solution, even if it was a sort of sumproduct formula....

Bob Phillips
02-18-2010, 01:44 AM
Good to see that we arrived at a solution, even if it was a sort of sumproduct formula....

:razz2: