Excel 2007 : groupby and distinct

Hi,

I'm trying to find the count of unique/distinct transactions which contain the item key and have sum = 0 from the columns below.

trans seq key item sum
1213 1 29319280 106369 0
1214 1 1899446 106369 0
1220 1 125635869 106369 0
1221 1 125635875 106369 0
1221 2 125635875 202 2.25
1224 1 79531558 106369 0
1225 1 79531571 106369 0
1225 2 79531571 82164 3.99
1226 3 79531571 666666 2

So in the above example i would get a count of 4. In sql, it would be a select distinct with groupby and a filter condition on item. Any idea on replication of this in excel?

Hi kumarsubram,

Pivot Tables will allow you to group and count and filter on zero. See the attached to see if it does what you need.