Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula question
Hi All, Im new to posting on this forum and have learnt alot viewing it frequently! I have a query with regards to excel formula. If not allowed to post queries, i do apologise. Please see attached jpg. The data is on the left. On the right is a "summary" of this data that i need to automate into formulas (Everything in RED needs to be dynamic). It also needs to stay in this exact format. No additional cells or fields can be created. Can anybody assist? Thanks in advance. Regards Jarad. +-------------------------------------------------------------------+ |Filename: Query.JPG | |Download: http://www.excelforum.com/attachment.php?postid=4409 | +-------------------------------------------------------------------+ -- Jarad ------------------------------------------------------------------------ Jarad's Profile: http://www.excelforum.com/member.php...o&userid=32072 View this thread: http://www.excelforum.com/showthread...hreadid=518209 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula question
No attached diagram.
Try to give an example of the data in your message and then a simple statement of what is needed. best wishes (we want to help!) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jarad" wrote in message ... Hi All, Im new to posting on this forum and have learnt alot viewing it frequently! I have a query with regards to excel formula. If not allowed to post queries, i do apologise. Please see attached jpg. The data is on the left. On the right is a "summary" of this data that i need to automate into formulas (Everything in RED needs to be dynamic). It also needs to stay in this exact format. No additional cells or fields can be created. Can anybody assist? Thanks in advance. Regards Jarad. +-------------------------------------------------------------------+ |Filename: Query.JPG | |Download: http://www.excelforum.com/attachment.php?postid=4409 | +-------------------------------------------------------------------+ -- Jarad ------------------------------------------------------------------------ Jarad's Profile: http://www.excelforum.com/member.php...o&userid=32072 View this thread: http://www.excelforum.com/showthread...hreadid=518209 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula question
Slightly upside down
E2: Total F2: =SUMPRODUCT((A2:A20<"")/COUNTIF(A2:A20,A2:A20&"")) G2: =SUMPRODUCT((B2:B20<"")/COUNTIF(B2:B20,B2:B20&"")) F3: = A2 G3: =IF(F3="","",SUM(--(FREQUENCY(IF($A$2:$A$20=F3,MATCH($B$2:$B$20,$B$2: $B$20,0 )),ROW(INDIRECT("1:"&ROWS($B$2:$B$20))))0))) F4: =IF(ISERROR(MATCH(0,COUNTIF(F$3:F3,$A$2:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$2:$A$20),"",$A$2:$A$20),MATCH( 0,COUNTIF(F$3:F3,$A$2:$A$2 0&""),0))) G3 and F4 are array formulae, which should be committed with Ctrl-Shift-Enter, not just Enter. Copy F4 down as far as you need, then copy G3 down to the same row. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Jarad" wrote in message ... Hi All, Im new to posting on this forum and have learnt alot viewing it frequently! I have a query with regards to excel formula. If not allowed to post queries, i do apologise. Please see attached jpg. The data is on the left. On the right is a "summary" of this data that i need to automate into formulas (Everything in RED needs to be dynamic). It also needs to stay in this exact format. No additional cells or fields can be created. Can anybody assist? Thanks in advance. Regards Jarad. +-------------------------------------------------------------------+ |Filename: Query.JPG | |Download: http://www.excelforum.com/attachment.php?postid=4409 | +-------------------------------------------------------------------+ -- Jarad ------------------------------------------------------------------------ Jarad's Profile: http://www.excelforum.com/member.php...o&userid=32072 View this thread: http://www.excelforum.com/showthread...hreadid=518209 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel division formula question | Excel Worksheet Functions | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Excel auto calculation formula question. | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
In excel the formula bar behaves differently on different machines | Excel Discussion (Misc queries) |