Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Problem which includes Sum,If,Frequency,match and row
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Problem which includes Sum,If,Frequency,match and row
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'!D38:D1800="North",IF('Total Turbines Arrs'!K38:K180010,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! In article , "BigH" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Problem which includes Sum,If,Frequency,match and row
=SUM(IF(FREQUENCY(IF((D38:D1800="North")*(E38:E180 0<"")*(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) "BigH" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|