trying to COUNT occurrences when certain criteria is met

[Guest]

Bob,
Thanks for your reply but it did not assist. I received the #value response.
Perhaps someone can assist with this. See the initial question and Bob's
reply after my brief example.
Thanks
Allan

I will give you a brief example of what I am after.

Table A1 = 320
A2 = 767
A3 = 73H (NON NUMERIC VALUE)
A4 = 744
A5 = 737

The data sheet E1 = 320 L1 = 100
E2 = 737 L2 = 99
E3 = 320 L3 = 121
E4 = 747 L4 = 35
E5 = 320 L5 = 190
E6 = 767 L6 = 130
E7 = 737 L7 = 145

The result that I would expect is
320 = 2 (only 2 occurrecnces >120)
767 = 1 (only 1 occurrence > 120)
73H = 0 ( no matching data)
744 = 0 ( no OCCURRENCE > 120)
737 = 1 ( only 1 occurrence > 120)

I hope that this small example explains my problem.
By the way, what does -- indicate or mean?
Thanks again.
Allan

 

[Guest]

Try this and copy down for Table!A2 etc:

=SUMPRODUCT(--($E$1:$E$10000=Table!A1),--($L$1:$L$10000>120))

-- converts True False to 1/0 so calculations/counts can be done.

HTH

 

[Bob Phillips]

Sorry, I read that as matching all values not singletons. Try

=SUMPRODUCT(--(E1:E10000=320),--(L1:L10000>120))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Bernie Deitrick]

Allan,

Try

=SUMPRODUCT((A1 = $E$1:$E$3000)*($L$1:$L$3000>120))

Copy down to match the number of entries you have in cells A1:A15

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks to Toppers, Bob and Bernie. Your solutions worked and saved me plenty
of time and made the spreadsheet work a lot quicker.
Thanks again
Allan