Conditional count

[kaushiksarda]

x y
a 11
b 11
c 11
a 12
c 12
d 12
a 13
b 13
d 13

I want to find out how many times a and b are together for all values of y. For example in the above table a and b are together 2 times i.e. when y=11 and y=13 you have a and b in the x column. I want the count of when a and b are together. I have a database consisting of more than 100,000 lines. Please help

[teylyn]

Hello kaushiksarda,

you can use a formula like

=SUMPRODUCT(--(A1:A100="a"),--(A2:A101="b"))

Note that both arrays have 100 rows, but the first array starts in row 1 and the second array starts in row 2. This will deliver a count of the rows where a is immediately followed by b.

Does that help?

cheers

[kaushiksarda]

Hello kaushiksarda,

you can use a formula like

=SUMPRODUCT(--(A1:A100="a"),--(A2:A101="b"))

Note that both arrays have 100 rows, but the first array starts in row 1 and the second array starts in row 2. This will deliver a count of the rows where a is immediately followed by b.

Does that help?

cheers

Hi,

Want I want is to find out the count for occurrence of a and b together for unique values of y.

When y=11 a and b have a occurrence. When y = 13 a and b have another occurrence.

b is not always followed after a. Is there some way we can do this. the sum product formula does not work.

[ConneXionLost]

How about this:

Please Login or Register  to view this content.

Cheers,

[windknife]

Try this,

Please Login or Register  to view this content.

committed with Ctrl+Shift+Enter

HTH,
windknife

[kaushiksarda]

Thanks man,

your formula works.. it was a great help

Try this,

Please Login or Register  to view this content.

committed with Ctrl+Shift+Enter

HTH,
windknife

[kaushiksarda]

Thanks man,

your formula works.. it was a great help

Hello again,

the formula works perfectly but my dataset has 200,000 lines and excel produces an error saying excel ran out of resources while attempting to calculate one or more formulas. IS there any way around it.

[windknife]

Maybe use tow help columns to deal with it.

1. select C1:C20000
=IF($A$1:$A20000="a",$B$1:$B20000,"")
committed with CSE
2. select D1:D20000
=IF($A$1:$A20000="b",$B$1:$B20000,"")
committed with CSE
3. E1

Please Login or Register  to view this content.

committed with CSE.

HTH,
windknife

A small sample is attached .