sumif across multiple sheets in excel 2007 based on a conditio
in [Functions]
|
From: Max on 13 May 2010 02:54 Biff, Neat solution Could you give a brief explanation as to what's happening in the FREQUENCY bit in your: > =INDEX(FREQUENCY(Saturday:Friday!A1:A10,0),2) Thanks -- Max Singapore ---
From: T. Valko on 13 May 2010 10:14 FREQUENCY returns an array of "count ifs" based on the bins argument. Let's see how this works on the following data. A1 = 0.5 A2 = 0 A3 = 0 A4 = 1 A5 = 2 We want a count of cells that are greater than 0. =INDEX(FREQUENCY(A1:A5,0),2) Returns 3 In this case we have just a single bin, 0. FREQUENCY will return two "count ifs". Count if A1:A5 is less than or equal to 0 = 2 (A2, A3) Count if A1:A5 is greater than 0 = 3 (A1, A4, A5) So, we have: =INDEX({2;3},2) We want the count of cells greater than 0 which is the second element of the array of "count ifs" so we tell INDEX to return that 2nd element. =INDEX({2;3},2) Returns 3 So: =INDEX(FREQUENCY(A1:A5,0),2) =3 Of course, if the range to be counted is on a single sheet then a simple COUNTIF(A1:A5,">0") will do. However, in the OP's application the range is multiple sheets. FREQUENCY can handle references across multiple sheets, 3D references and multiple area references (on the same sheet). References across multiple sheets: =INDEX(FREQUENCY(Saturday:Friday!A1:A10,0),2) 3D references: =INDEX(FREQUENCY(Saturday:Friday!A1:C10,0),2) Multiple area references: =INDEX(FREQUENCY((A1:A10,B22:B25,X100:Z120),0),2) -- Biff Microsoft Excel MVP "Max" <demechanik(a)yahoo.com> wrote in message news:BFA78854-15B2-43E4-A4E6-61273C7C531E(a)microsoft.com... > Biff, > Neat solution > Could you give a brief explanation as to what's happening in the FREQUENCY > bit in your: >> =INDEX(FREQUENCY(Saturday:Friday!A1:A10,0),2) > Thanks > -- > Max > Singapore > ---
From: Max on 13 May 2010 13:28 Thanks for the explanations, Biff
From: T. Valko on 13 May 2010 14:42 You're welcome, Max! -- Biff Microsoft Excel MVP "Max" <demechanik(a)yahoo.com> wrote in message news:u1PsKHs8KHA.4604(a)TK2MSFTNGP04.phx.gbl... > Thanks for the explanations, Biff
From: dave on 23 May 2010 22:34 Thanks for the file Stanley and that worked perfect... Sorry for the delay in responding... went on business trip... and Biff thanks for the reply as well, although I could not follow exactly what you had suggested... Dave -- thanks for your help Dave "stanleydgromjr" wrote: > > Dave, > > Detach/open workbook "*Countif Saturday thru Friday sheets - Dave - > SDG10.xlsx*" for a posable solution. > > > If this is not what you are looking for then please post your > workbook. > > > +-------------------------------------------------------------------+ > |Filename: Countif Saturday thru Friday sheets - Dave - SDG10.xlsx | > |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=581| > +-------------------------------------------------------------------+ > > -- > stanleydgromjr > ------------------------------------------------------------------------ > stanleydgromjr's Profile: http://www.thecodecage.com/forumz/member.php?u=503 > View this thread: http://www.thecodecage.com/forumz/showthread.php?t=202408 > > http://www.thecodecage.com/forumz > > . >
|
Pages: 1 Prev: Using Custom Views with Protected Worksheets Next: How to look up a value? |