Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF for specific clients
Your question doesn't clearly convey the layout of your BTA sheet, but let's
assume that Column B is for Jan, C is for Feb, etc., and that row 1 contains headers. Data starts in row 2 To find the correct row that you want to sum, use the MATCH() function. Change the client ref to whatever cell you're using to hold the client ref. =MATCH(client ref,BTA!$A$2:$a$500,0) Now you want to sum all the completed months in that row, so we'll incorporate the MATCH() formula into the SUM() =SUM(OFFSET(BTA!$A$1,MATCH(client ref,BTA!A2:a500,0),1,1,MONTH(TODAY())-1)) Finally, to get the portion of the current month, we'll simplify by using one of the Analysis Toolpack functions [Tools- Add-ins... and make sure the Analsysis Toolpack is checked]. +OFFSET(BTA!$A$1,MATCH(client ref,BTA!A2:a500,0),1,MONTH(TODAY()),1)/DAY(EMONTH(TODAY(),0))*DAY(E1) In simple terms, the MATCH() function says how many rows to move down from the top of the BTA sheet to find the proper client's row. The SUM(OFFSET()) will sum the number of columns representing the completed months, starting with column B. You may need to replace the 3 TODAY() functions in the formula with a reference to your cell E1 "Sarah (OGI)" wrote: I have the following formula which adds the budget values for all complete months of the current year together with a pro-rata'd budget value for the number of days in the current (incomplete) month. =SUMIF('Summary 2010 GWP phasing'!$B$4:$M$4,"<="&DATE(YEAR(TODAY()),MONTH(T ODAY()),DAY(0)),'Summary 2010 GWP phasing'!$B$24:$M$24)+((SUMIF('Summary 2010 GWP phasing'!$B$4:$M$4,"="&DATE(YEAR(TODAY()),MONTH(TO DAY()),DAY(1)),'Summary 2010 GWP phasing'!$B$24:$M$24)/DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)))*DAY($ E$1)) For example, I am creating a report up to the 13th Feb so I need to combine the total budget for Jan with 13 days of the Feb budget. The formula works fine and does what I need. However, I now need to do this for a list of specific clients but I'm not sure how to factor in a reference to the relevant client. I was thinking a vlookup but I'm not sure. I have a list of clients in a sheet labelled League Table and the budget figures in a sheet labelled BTA. The BTA sheet shows client refs in column A and 12 (monthly) budget figures in subsequent columns. Can anyone tell me how best to incorporate a specific client reference. Many thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with criteria being a specific cell | Excel Worksheet Functions | |||
sumif for a specific number of cells | Excel Discussion (Misc queries) | |||
Transfer specific clients. | Excel Discussion (Misc queries) | |||
SUMIF, with specific search criteria | Excel Worksheet Functions | |||
I need to do a sumif on data that [contains] a specific string. | Excel Worksheet Functions |