Two search arguments in array formula
 

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 didnt work, the second version did returned result but incorekt.
Any suggestions!
Thank you,
Guntars

Two search arguments in array formula
 

I think you should take a look at this:
http://www.contextures.com/xlFunctio...tml#SumProduct

Good luck,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Guntars" wrote:

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 didnt work, the second version did returned result but incorekt.
Any suggestions!
Thank you,
Guntars

Two search arguments in array formula
 

Try this...

Normally entered:

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

--
Biff
Microsoft Excel MVP


"Guntars" wrote in message
...
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","BE CW"}),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_eve nts))0))}
But they didn't work, the second version did returned result but incorekt.
Any suggestions!
Thank you,
Guntars