Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Formulas
I have a spreadsheet and I need a formul to calculate a sum based on various criteria and a count of unique entries based on criteria. My current formulas are as follows: SUM =SUMIF(V8:V500,"Yes-AFT",P8:P524) COUNT {=SUM(IF(FREQUENCY(IF((A8:A500<"")*(V8:V500="Yes-AFT"),MATCH(A8:A500,A8:A500,0)),ROW(INDIRECT("1:"& ROWS(A8:A500))))0,1))} I need to adapt these to take into account another condition, i.e. where the derived quarter in range N8:N524 is equal to that shown in a control field in cell J2 The quaters are currently derived from date entry using the following formula: =IF(M8 0,"Q"&INT(1+MOD(MONTH(M8)-4,12)/3)&" "&YEAR(M8)-1+(MONTH(M8)=4),"") Any help would be greatly appreciated as this is driving me crazy Many thanks -- IanEmery ------------------------------------------------------------------------ IanEmery's Profile: http://www.excelforum.com/member.php...o&userid=35714 View this thread: http://www.excelforum.com/showthread...hreadid=561055 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Formulas
IanEmery wrote:
I have a spreadsheet and I need a formul to calculate a sum based on various criteria and a count of unique entries based on criteria. My current formulas are as follows: SUM =SUMIF(V8:V500,"Yes-AFT",P8:P524) COUNT {=SUM(IF(FREQUENCY(IF((A8:A500<"")*(V8:V500="Yes-AFT"),MATCH(A8:A500,A8:A500,0)),ROW(INDIRECT("1:"& ROWS(A8:A500))))0,1))} I need to adapt these to take into account another condition, i.e. where the derived quarter in range N8:N524 is equal to that shown in a control field in cell J2 The quaters are currently derived from date entry using the following formula: =IF(M8 0,"Q"&INT(1+MOD(MONTH(M8)-4,12)/3)&" "&YEAR(M8)-1+(MONTH(M8)=4),"") Any help would be greatly appreciated as this is driving me crazy Many thanks I think you could use a SUMPRODUCT formula or a INDEX MATCH (array entered) formula. If you need more help you could upload an example file to www.savefile.com -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Formulas
Try...
=SUMPRODUCT(--(N8:N524=J2),--(V8:V524="Yes-AFT"),P8:P524) and =SUM(IF(FREQUENCY(IF(A8:A524<"",IF(N8:N524=J2,IF( V8:V524="Yes-AFT",MATCH (A8:A524,A8:A524,0)))),ROW(A8:A524)-ROW(A8)+1),1)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , IanEmery wrote: I have a spreadsheet and I need a formul to calculate a sum based on various criteria and a count of unique entries based on criteria. My current formulas are as follows: SUM =SUMIF(V8:V500,"Yes-AFT",P8:P524) COUNT {=SUM(IF(FREQUENCY(IF((A8:A500<"")*(V8:V500="Yes-AFT"),MATCH(A8:A500,A8:A500, 0)),ROW(INDIRECT("1:"&ROWS(A8:A500))))0,1))} I need to adapt these to take into account another condition, i.e. where the derived quarter in range N8:N524 is equal to that shown in a control field in cell J2 The quaters are currently derived from date entry using the following formula: =IF(M8 0,"Q"&INT(1+MOD(MONTH(M8)-4,12)/3)&" "&YEAR(M8)-1+(MONTH(M8)=4),"") Any help would be greatly appreciated as this is driving me crazy Many thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Formulas
Thanks Franz Unfortunately I am unable to uploads a file due to network restrictions () Would it be possible to provide an eample of your ideas. Many thanks :) -- IanEmery ------------------------------------------------------------------------ IanEmery's Profile: http://www.excelforum.com/member.php...o&userid=35714 View this thread: http://www.excelforum.com/showthread...hreadid=561055 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Formulas
Hi Domenic :) Thanks for your reply - your first formula works beautifully. However I receive a standard MS Excel "error in formula" dialogue box when committing the second formula using Ctrl/Shift/Enter. Any ideas -- IanEmery ------------------------------------------------------------------------ IanEmery's Profile: http://www.excelforum.com/member.php...o&userid=35714 View this thread: http://www.excelforum.com/showthread...hreadid=561055 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Formulas
If you copied and pasted the formula from the post into your worksheet,
make sure hard returns haven't been added to the formula... Is this the case? In article , IanEmery wrote: Hi Domenic :) Thanks for your reply - your first formula works beautifully. However I receive a standard MS Excel "error in formula" dialogue box when committing the second formula using Ctrl/Shift/Enter. Any ideas |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Formulas
Thanks very much Domenic - you were right all along Embaressed that I did something so naive Should never have doubted you. Thanks again :) -- IanEmery ------------------------------------------------------------------------ IanEmery's Profile: http://www.excelforum.com/member.php...o&userid=35714 View this thread: http://www.excelforum.com/showthread...hreadid=561055 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change column letters to correct ones in many formulas automatically? | Links and Linking in Excel | |||
how can i get formulas in excel to copy and paste? | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |