SUM by unique reference
 

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?

SUM by unique reference
 

Please tell us more about how the worksheet is set out.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"WendyUK" wrote in message
...
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?

SUM by unique reference
 

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

--
Gary''s Student - gsnu200732


"WendyUK" wrote:

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?

SUM by unique reference
 

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

"WendyUK" wrote:

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?

SUM by unique reference
 

=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

"WendyUK" wrote:

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?

SUM by unique reference
 

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

"Bernard Liengme" wrote:

Please tell us more about how the worksheet is set out.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"WendyUK" wrote in message
...
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?

SUM by unique reference
 

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.

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200732


"WendyUK" wrote:

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?

SUM by unique reference
 

=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.


"WendyUK" wrote:

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

"Bernard Liengme" wrote:

Please tell us more about how the worksheet is set out.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"WendyUK" wrote in message
...
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?

SUM by unique reference
 

try
=sumif(URN_range,unique_urn,Value_range)

"WendyUK" wrote:

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

"Bernard Liengme" wrote:

Please tell us more about how the worksheet is set out.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"WendyUK" wrote in message
...
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?

SUM by unique reference
 

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.

"Toppers" wrote:

=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.


"WendyUK" wrote:

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

"Bernard Liengme" wrote:

Please tell us more about how the worksheet is set out.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"WendyUK" wrote in message
...
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?

SUM by unique reference
 

What is your formula?

"WendyUK" wrote:

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.

"Toppers" wrote:

=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.


"WendyUK" wrote:

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

"Bernard Liengme" wrote:

Please tell us more about how the worksheet is set out.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"WendyUK" wrote in message
...
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?

SUM by unique reference
 

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

"Toppers" wrote:

What is your formula?

"WendyUK" wrote:

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.

"Toppers" wrote:

=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.


"WendyUK" wrote:

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

"Bernard Liengme" wrote:

Please tell us more about how the worksheet is set out.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"WendyUK" wrote in message
...
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?

SUM by unique reference
 

=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)

"WendyUK" wrote:

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

"Toppers" wrote:

What is your formula?

"WendyUK" wrote:

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.

"Toppers" wrote:

=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.


"WendyUK" wrote:

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

"Bernard Liengme" wrote:

Please tell us more about how the worksheet is set out.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"WendyUK" wrote in message
...
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?

SUM by unique reference
 

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?

"Toppers" wrote:

=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)

"WendyUK" wrote:

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

"Toppers" wrote:

What is your formula?

"WendyUK" wrote:

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.

"Toppers" wrote:

=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.


"WendyUK" wrote:

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

"Bernard Liengme" wrote:

Please tell us more about how the worksheet is set out.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"WendyUK" wrote in message
...
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?

SUM by unique reference
 

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

"WendyUK" wrote:

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?

"Toppers" wrote:

=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)

"WendyUK" wrote:

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

"Toppers" wrote:

What is your formula?

"WendyUK" wrote:

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.

"Toppers" wrote:

=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.


"WendyUK" wrote:

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

"Bernard Liengme" wrote:

Please tell us more about how the worksheet is set out.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"WendyUK" wrote in message
...
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?

SUM by unique reference
 

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

In B2:
=IF(ISERR(SMALL(IF(FREQUENCY(MATCH(ref,ref,0),MATC H(ref,ref,0))0,ROW(INDIRECT("1:"&ROWS(ref)))),ROW S($1:1))),"",INDEX(ref,SMALL(IF(FREQUENCY(MATCH(re f,ref,0),MATCH(ref,ref,0))0,ROW(INDIRECT("1:"&ROW S(ref)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

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

Just normal enter
copy down



"WendyUK" wrote:

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?