Count the number of times a value greater than x is in an array

**Ainsley** · 07-08-2011, 06:34 AM

Hi,

I have a list of people who download music, it shows their name in column A, then the number of songs downloaded in B.

They can appear in the list many times and can download songs once or many times.

I would like to count the number of times a spedific person downloads more than X songs.

Ive gone mad with lookups and if formulas and need some help.

I just want to say for person "joe blogs" and X = 10 it will tell me how many times Joe downloaded 10 or more songs.

Thanks.

**DonkeyOte** · 07-08-2011, 06:39 AM

Originally Posted by Ainsley

They can appear in the list many times and can download songs once or many times.

On which basis - does X represent unique songs ?

Also please clarify as to the XL version in use.

**TMS** · 07-08-2011, 06:39 AM

Please post a sample workbook with some typical data.

Please also update your profile and/or indicate which version of Excel you are using.

With Excel 2003, you'd use SUMPRODUCT; with Excel 2007, you'd use SUMIFS (or SUMPRODUCT)

Regards

**brokenbiscuits** · 07-08-2011, 06:43 AM

If you have Excel 2007 onwards:

=COUNTIFS(A:A,"Joe Bloggs",B:B,">10")

Otherwise:

=SUMPRODUCT(--(A:A="Joe Bloggs")*(B:B>10))

(this might be slow to calculate, so better to give a smaller range rather than a whole column - i.e. A1:A1000 and B1:B1000)

Hope this helps.

**Ainsley** · 07-08-2011, 06:44 AM

Im using 2007.

In my table I could have loads of people in column A, and in B it will have the number of songs they downloaded.

People can appear in the list several times and they can download 1 or more songs. So Joe Blogs can appear say twice, once he downloads 10 songs, the second time 20.

I would like to be able to lookup how many times Joe Blogs downloaded more than 15 songs, so in my example it would be Once. That is the person downloaded more than 15 songs only once.

Hope that makes sence.

**DonkeyOte** · 07-08-2011, 06:47 AM

Originally Posted by brokenbiscuits

If you have Excel 2007 onwards

...
Otherwise:

=SUMPRODUCT(--(A:A="Joe Bloggs")*(B:B>10))

(this might be slow to calculate, so better to give a smaller range rather than a whole column - i.e. A1:A1000 and B1:B1000)

@bb, you can't use entire column references with SUMPRODUCT prior to XL2007 (would generate #NUM! error so have to be restricted anyway)

(also FWIW the double unary coercion is unnecessary given multiplication of arrays)

**brokenbiscuits** · 07-08-2011, 07:17 AM

Yeah, I never really fully got my head around SUMPRODUCT before we switched to 2007, especially the coercion stuff, and thankfully now I don't ever really need to!

Ainsley, from your further explanation, the 2007 example I posted above should work, no?