Excel Formula question

[Jarad]

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.

[Bernard Liengme]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>