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

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

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