Formula Problem which includes Sum,If,Frequency,match and row

[BigH]

Hi there,
=SUM(IF(FREQUENCY(IF(('Total Turbines Arrs'!E38:E1800<>"")*('Total Turbines
Arrs'!K38:K1800<-10),MATCH('Total Turbines Arrs'!E38:E1800,'Total Turbines
Arrs'!E38:E1800,0)),ROW('Total Turbines Arrs'!E38:E1800)-ROW('Total Turbines
Arrs'!E38)-1)>0,1))

this array formula works perfect, what it basically does is lookup column E,
which has lots of part numbers some are duplicated many times, it compares
these parts against column K, and adds up how many parts are greater than
10.

what i want to do is add a third variable column D, which shows regions
North south east west, each region has specific part numbers which belong to
that region. What I want to do is find out for each region what the number
of parts are greater than 10 days. so in effect the formula must take into
consideration column D, E and K

tia BigH

 

[Domenic]

Based on the information you've provided, I don't see how your formula
can return the correct result. Also, your formula seems to have an
error. It should be +1 at the end, not -1.

In any case, if you want to count the unique entries in Column E where
Column D contains the region of interest, such as 'North', and Column K
contains a value greater than 10, try...

=SUM(IF(FREQUENCY(IF('Total Turbines Arrs'!E38:E1800<>"",IF('Total
Turbines Arrs'!D381800="North",IF('Total Turbines
Arrs'!K38:K1800>10,MATCH('Total Turbines Arrs'!E38:E1800,'Total Turbines
Arrs'!E38:E1800,0)))),ROW('Total Turbines Arrs'!E38:E1800)-ROW('Total
Turbines Arrs'!E38)+1)>0,1))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Bob Phillips]

=SUM(IF(FREQUENCY(IF((D381800="North")*(E38:E1800<>"")*(K38:K1800<-10),
MATCH(E38:E1800,E38:E1800,0)),ROW(E38:E1800)-ROW(E38)-1)>0,1))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)