Evaluating data within an array

[Tracey]

I'm looking for a formula that will determine if a value
is present in a range (column) and then look in that row
and determine if a cell in that row equals a certain
value. I know I can use {if(a1:a150="yellow",true,
false)} to do the first part.

How can I modify this to then have it look at the row
with "yellow" in the first column to see if say blue is in
column E of the same row. If this yellow/blue scenerio
occurs multiple times in a table, how can I make it count
the number of times it occurs?

Any ideas? Thanks

 

[Tom Ogilvy]

=sumproduct((A1:A150="Yellow")*(E1:E150="Blue"))

will give you the number of rows that meet the criteria.

Regards,
Tom Ogilvy

 

[Jason Morin]

Try:

=SUMPRODUCT((A1:A150="yellow")*(COUNTIF(INDIRECT(ROW(rng)
&":"&ROW(rng)),"blue")>0))

where "rng" represents the rows 1-150 (1:150). BTW - not
an array!

HTH
Jason
Atlanta, GA

 

[Tracey]

Thanks