software

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
quot;summaryquot; 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: www.excelforum.com/attachment.php?postid=4409 |
-------------------------------------------------------------------

--
Jarad
------------------------------------------------------------------------
Jarad's Profile: www.excelforum.com/member.php...oamp;userid=32072
View this thread: www.excelforum.com/showthread...hreadid=518209No 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

quot;Jaradquot; gt; wrote in
message ...
gt;
gt; Hi All,
gt;
gt; Im new to posting on this forum and have learnt alot viewing it
gt; frequently!
gt;
gt; I have a query with regards to excel formula. If not allowed to post
gt; queries, i do apologise.
gt;
gt; Please see attached jpg. The data is on the left. On the right is a
gt; quot;summaryquot; of this data that i need to automate into formulas
gt; (Everything in RED needs to be dynamic).
gt; It also needs to stay in this exact format. No additional cells or
gt; fields can be created.
gt; Can anybody assist?
gt;
gt; Thanks in advance.
gt; Regards
gt; Jarad.
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: Query.JPG |
gt; |Download: www.excelforum.com/attachment.php?postid=4409 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; Jarad
gt; ------------------------------------------------------------------------
gt; Jarad's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32072
gt; View this thread: www.excelforum.com/showthread...hreadid=518209
gt;
Slightly upside down

E2: Total
F2: =SUMPRODUCT((A2:A20lt;gt;quot;quot;)/COUNTIF(A2:A20,A2:A20amp;quot;quot;))
G2: =SUMPRODUCT((B2:B20lt;gt;quot;quot;)/COUNTIF(B2:B20,B2:B20amp;quot;quot;))

F3: = A2
G3:
=IF(F3=quot;quot;,quot;quot;,SUM(--(FREQUENCY(IF($A$2:$A$20=F3,MATCH($B$2:$B$20,$B$2: $B$20,0
)),ROW(INDIRECT(quot;1:quot;amp;ROWS($B$2:$B$20))))gt;0)))

F4: =IF(ISERROR(MATCH(0,COUNTIF(F$3:F3,$A$2:$A$20amp;quot;quot;), 0)),quot;quot;,
INDEX(IF(ISBLANK($A$2:$A$20),quot;quot;,$A$2:$A$20),MATCH( 0,COUNTIF(F$3:F3,$A$2:$A$2
0amp;quot;quot;),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)

quot;Jaradquot; gt; wrote in
message ...
gt;
gt; Hi All,
gt;
gt; Im new to posting on this forum and have learnt alot viewing it
gt; frequently!
gt;
gt; I have a query with regards to excel formula. If not allowed to post
gt; queries, i do apologise.
gt;
gt; Please see attached jpg. The data is on the left. On the right is a
gt; quot;summaryquot; of this data that i need to automate into formulas
gt; (Everything in RED needs to be dynamic).
gt; It also needs to stay in this exact format. No additional cells or
gt; fields can be created.
gt; Can anybody assist?
gt;
gt; Thanks in advance.
gt; Regards
gt; Jarad.
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: Query.JPG |
gt; |Download: www.excelforum.com/attachment.php?postid=4409 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; Jarad
gt; ------------------------------------------------------------------------
gt; Jarad's Profile:
www.excelforum.com/member.php...oamp;userid=32072
gt; View this thread: www.excelforum.com/showthread...hreadid=518209
gt;