sumif formula
in [Functions]
|
Prev: result given from content of selected cells.
Next: need formula to add two different criteria in a range
From: Vince on 29 Apr 2010 09:49 I have a summary sheet and a detail sheet. On the summary sheet I am trying to sum columns (E - O) from the detail sheet that are less than or equal to a date for a specific account in column A on the detail sheet. The problem is that I do not know the row that the account falls on. So far I have SUMIF(Budget_Cons!$E$4:$O$4,"<"&"="month,OFFSET(E4,MATCH($A9,A:A,0),0)) Thanks for your help!
From: Luke M on 29 Apr 2010 10:47 =SUMIF(Budget_Cons!$E$4:$O$4,"<="&MONTH(OFFSET(E4,MATCH($A9,A:A,0),))) This will sum everything in E4:O4 that is less than the month of the date found by the OFFSET function. Presumably, you'll actually want to sum another range, something like: =SUMIF(Budget_Cons!$E$4:$O$4,"<="&MONTH(OFFSET(E4,MATCH($A9,A:A,0),)),Range_to_Sum) -- Best Regards, Luke M "Vince" <Vince(a)discussions.microsoft.com> wrote in message news:F8BC95F5-649B-4640-8E9C-5639F44FCCCD(a)microsoft.com... >I have a summary sheet and a detail sheet. On the summary sheet I am >trying > to sum columns (E - O) from the detail sheet that are less than or equal > to a > date for a specific account in column A on the detail sheet. The problem > is that I do not know the row that the account falls on. So far I have > SUMIF(Budget_Cons!$E$4:$O$4,"<"&"="month,OFFSET(E4,MATCH($A9,A:A,0),0)) > > Thanks for your help!
From: Steve Dunn on 29 Apr 2010 10:46
Do columns E-O actually contain dates, month numbers, or something else, and should we be summing them, or just counting them? Locating the account to work with is the least of the problems here. Taking your requirements as you've described them: =SUMIF(OFFSET(Budget_Cons!$E$4:$O$4,MATCH(E4,Budget_Cons!$A$4:$A$100,0)-1,0),"<="&month) would work. "Vince" <Vince(a)discussions.microsoft.com> wrote in message news:F8BC95F5-649B-4640-8E9C-5639F44FCCCD(a)microsoft.com... >I have a summary sheet and a detail sheet. On the summary sheet I am >trying > to sum columns (E - O) from the detail sheet that are less than or equal > to a > date for a specific account in column A on the detail sheet. The problem > is that I do not know the row that the account falls on. So far I have > SUMIF(Budget_Cons!$E$4:$O$4,"<"&"="month,OFFSET(E4,MATCH($A9,A:A,0),0)) > > Thanks for your help! |