SUM by unique reference

W

WendyUK

Hi, I am trying to bring out multiple totals based on a Reference Number with
an unknown number of lines.

i.e ref 1956 could have 8 lines and ref 3466 could have 5 lines and I want
to show a total for each unique reference - can anyone help me?
 
G

Gary''s Student

Consider using a pivot table. If our data is, for example:

reference
1956
1956
1956
1956
1956
1956
1956
1956
3466
3466
3466
3466
3466
2134
2134
6789

then a simple pivot table would give:

Count of reference
reference Total
1956 8
2134 2
3466 5
6789 1
Grand Total 16
 
B

bj

what do you mean by reference Number?

one possibilty would be to select each ref set and insert-name-define and
name each ref set
for example ref1956
you could then use =sum(ref1956) to get a sum for that data set
 
T

Toppers

=SUMIF($A$1:$A$100,1956,$B$1:$B$100)

will total column B where reference in A is (numeric) 1956

or

=SUMPRODUCT(--($A$1:$A$100=1956),$B$1:$B$100)

Put 1956 in quotes ("1956") if data is TEXT

You could the reference in a cell e.g H1 and use:

=SUMPRODUCT(--($A$1:$A$100=H1),$B$1:$B$100)


HTH
 
W

WendyUK

i.e. I have 7 columns:
A/C ,Name, Type, Code, Description, Value, URN Ref

The URN Ref is assigned by the system and is different for each transaction,
but each transaction can have multiple lines. I wish to have a total of the
value field for each unique URN Ref which is the number the system assigns
when the transaction is input.

Thank you in advance
 
W

WendyUK

This is an option and I thank you, but I want to try and show everything on
one spreadsheet as their will then be extra analysis.
 
T

Toppers

=SUMPRODUCT(--($G$2:$G$100=URN Ref),$F$2:$F$100)

where URN Ref is a constant or value in a cell.

See my other reply.
 
B

bj

try
=sumif(URN_range,unique_urn,Value_range)

WendyUK said:
i.e. I have 7 columns:
A/C ,Name, Type, Code, Description, Value, URN Ref

The URN Ref is assigned by the system and is different for each transaction,
but each transaction can have multiple lines. I wish to have a total of the
value field for each unique URN Ref which is the number the system assigns
when the transaction is input.

Thank you in advance
Click to expand...
 
W

WendyUK

Thank you very much, but I am getting an error #Name? - I've tried it with
the physical field name that it uses to fetch the data and with the column
header name but it gives me the same error? what am I doing wrong?

Thank you again in advance.
 
T

Toppers

What is your formula?

WendyUK said:
Thank you very much, but I am getting an error #Name? - I've tried it with
the physical field name that it uses to fetch the data and with the column
header name but it gives me the same error? what am I doing wrong?

Thank you again in advance.
Click to expand...
 
T

Toppers

=SUMPRODUCT(--($N$3:$N$211="1956"),$M$3:$M$211)

if, for example ,your reference number is TEXT (value)1956

OR

set for example Z1="1956" (Z1 formatted as TEXT)

=SUMPRODUCT(--($N$3:$N$211=Z1),$M$3:$M$211)
 
W

WendyUK

The problem is that the reference number changes with each transaction, so I
could have transaction 1956, 1957, 2004, etc etc and these will be different
depending on the date range we request, although they do come through in
reference number order. So I want it to sum after each unique number. Is
there a GROUP command I can use?
 
T

Toppers

Can you send me a w/book with some sample data and explanation of what you
require to avoid this to and fro dialogue?

toppers <at> johntopley.fsnet.co.uk
 
T

Teethless mama

Try this:
"ref" is a define name range (ex: A1:A100)

In B2:
=IF(ISERR(SMALL(IF(FREQUENCY(MATCH(ref,ref,0),MATCH(ref,ref,0))>0,ROW(INDIRECT("1:"&ROWS(ref)))),ROWS($1:1))),"",INDEX(ref,SMALL(IF(FREQUENCY(MATCH(ref,ref,0),MATCH(ref,ref,0))>0,ROW(INDIRECT("1:"&ROWS(ref)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

In C2: =IF(B2="","",COUNTIF(ref,B2))

Just normal enter
copy down
 
Top