Count or Frequency formula needed

[Optimus_Primed036]

Hi all,

Wondering if anyone can help. I want to count how many times an event occurs. Each event has an individual ID but it can appear many times.
Once established i only want to include those with "IP" in the same row.

For example:

A2=100
A3=100
A4=200

B2=IP
B3=IP
B4=PE

The answer to this should be "1" as there is only one ID (100) with "IP" in same row even though it appears twice.

Hope that made sense.

 

[Optimus_Primed036]

Thanks to all who responded - Aladin your formula works fine. I must have a really slow computer because it was taking around 5 minutes to process the formula, and i thought it wasn't working.

Fazza thanks for the heads up on that approach - food for thought...

 

[Fazza]

Yes, on a large data table the formula approach may take five minutes (and bog down the whole spreadsheet every time calculations are done unless you used VBA to only update that formula as required. But it would still take minutes any time it was done): the query approach should be a fraction of a second.

 

[Aladin Akyurek]

Thanks to all who responded - Aladin your formula works fine. I must have a really slow computer because it was taking around 5 minutes to process the formula, and i thought it wasn't working.

Fazza thanks for the heads up on that approach - food for thought...

The range to process is too large. Hence the time it needs.