Two search arguments in array formula

G

Guntars

Hello,
With help of this forum I created this array formula:
{=SUM(--(FREQUENCY(IF((S_Shop_events="x")*(Event="5S"),ROW(S_Shop_events)),ROW(S_Shop_events))>0))}
Which basically states: count all the “x†in range called ‘S_Shop_events’
and count them in a cells where column is Event="5S". But what I need is to
search in columns Event="5S" and Event="BECW".
I was trying this formula:
{=SUM(--(FREQUENCY(IF((S_Shop_events="x")*(Event={"5S",â€BECWâ€}),ROW(S_Shop_events)),ROW(S_Shop_events))>0))}
And this:
{=SUM(--(FREQUENCY(IF((S_Shop_events="x")*OR((Event="5S"),(Event=â€BECWâ€)),ROW(S_Shop_events)),ROW(S_Shop_events))>0))}
But they didn’t work, the second version did returned result but incorekt.
Any suggestions!
Thank you,
Guntars
 
T

T. Valko

Try this...

Normally entered:

=SUMPRODUCT(--(S_Shop_events="x"),--(ISNUMBER(MATCH(Event,{"5S","BECW"},0))))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top