I have the following data in a worksheet
Client Currency Amount
x ‚¬ 95,000
x ‚¬ 80,000
y ‚¬ 65,000
z $ 70,000
z $ 20,000
az £ 15,000

the issue is if i use filter then the total (sum formula) will not apply on
filters it displays the amount of all entries, i can use SUMIF formula but
the problem is that there are more than one currency involved (my criteria
field), so is there any sort of combination of formula to filter the list and
get the desired result.

Use Subtotal

=SUBTOTAL(9,C2:C7)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Blackwar" wrote in message
...
I have the following data in a worksheet
Client Currency Amount
x ? 95,000
x ? 80,000
y ? 65,000
z $ 70,000
z $ 20,000
az £ 15,000

the issue is if i use filter then the total (sum formula) will not apply
on
filters it displays the amount of all entries, i can use SUMIF formula but
the problem is that there are more than one currency involved (my criteria
field), so is there any sort of combination of formula to filter the list
and
get the desired result.

thanks i already tried, sorry i should be more illustrative, actually i want
to get the result in the following format (with or without using filters), as
i mentioned that there are more than one currency involved:

cel no Client Currency Amount
a1 x ‚¬ 95,000
a2 x ‚¬ 80,000
a3 y ‚¬ 65,000
a4 z $ 70,000
a5 z $ 20,000
a6 az £ 15,000
--------------------------------
Total ‚¬ currency result
$ currency result
£ currency result

pls help

"Bob Phillips" wrote:

Use Subtotal

=SUBTOTAL(9,C2:C7)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Blackwar" wrote in message
...
I have the following data in a worksheet
Client Currency Amount
x ? 95,000
x ? 80,000
y ? 65,000
z $ 70,000
z $ 20,000
az £ 15,000

the issue is if i use filter then the total (sum formula) will not apply
on
filters it displays the amount of all entries, i can use SUMIF formula but
the problem is that there are more than one currency involved (my criteria
field), so is there any sort of combination of formula to filter the list
and
get the desired result.

You should have said.

Assuming that column A is the filtered column, then

=SUMPRODUCT((SUBTOTAL(3,OFFSET($A$1,ROW($A$2:$A$20 )-ROW($A$1),,1)))*(B2:B20=
"?"),C2:C20)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Blackwar" wrote in message
...
thanks i already tried, sorry i should be more illustrative, actually i
want
to get the result in the following format (with or without using filters),
as
i mentioned that there are more than one currency involved:

cel no Client Currency Amount
a1 x ? 95,000
a2 x ? 80,000
a3 y ? 65,000
a4 z $ 70,000
a5 z $ 20,000
a6 az £ 15,000
--------------------------------
Total ? currency result
$ currency result
£ currency result

pls help

"Bob Phillips" wrote:

Use Subtotal

=SUBTOTAL(9,C2:C7)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Blackwar" wrote in message
...
I have the following data in a worksheet
Client Currency Amount
x ? 95,000
x ? 80,000
y ? 65,000
z $ 70,000
z $ 20,000
az £ 15,000

the issue is if i use filter then the total (sum formula) will not
apply
on
filters it displays the amount of all entries, i can use SUMIF formula
but
the problem is that there are more than one currency involved (my
criteria
field), so is there any sort of combination of formula to filter the
list
and
get the desired result.

Dear Bob
Lot of thanks & appreciation Bob, sorry not to provide the complete details,
but your professinalism is highly appreciated. Your formula is really great

thanks again

"Bob Phillips" wrote:

You should have said.

Assuming that column A is the filtered column, then

=SUMPRODUCT((SUBTOTAL(3,OFFSET($A$1,ROW($A$2:$A$20 )-ROW($A$1),,1)))*(B2:B20=
"?"),C2:C20)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Blackwar" wrote in message
...
thanks i already tried, sorry i should be more illustrative, actually i
want
to get the result in the following format (with or without using filters),
as
i mentioned that there are more than one currency involved:

cel no Client Currency Amount
a1 x ? 95,000
a2 x ? 80,000
a3 y ? 65,000
a4 z $ 70,000
a5 z $ 20,000
a6 az £ 15,000
--------------------------------
Total ? currency result
$ currency result
£ currency result

pls help

"Bob Phillips" wrote:

Use Subtotal

=SUBTOTAL(9,C2:C7)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Blackwar" wrote in message
...
I have the following data in a worksheet
Client Currency Amount
x ? 95,000
x ? 80,000
y ? 65,000
z $ 70,000
z $ 20,000
az £ 15,000

the issue is if i use filter then the total (sum formula) will not
apply
on
filters it displays the amount of all entries, i can use SUMIF formula
but
the problem is that there are more than one currency involved (my
criteria
field), so is there any sort of combination of formula to filter the
list
and
get the desired result.

No worries, just teasing. The Euro symbol looked a bit odd in the
transcription though!

Bob

"Blackwar" wrote in message
...
Dear Bob
Lot of thanks & appreciation Bob, sorry not to provide the complete
details,
but your professinalism is highly appreciated. Your formula is really
great

thanks again

"Bob Phillips" wrote:

You should have said.

Assuming that column A is the filtered column, then


=SUMPRODUCT((SUBTOTAL(3,OFFSET($A$1,ROW($A$2:$A$20 )-ROW($A$1),,1)))*(B2:B20=
"?"),C2:C20)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Blackwar" wrote in message
...
thanks i already tried, sorry i should be more illustrative, actually
i
want
to get the result in the following format (with or without using
filters),
as
i mentioned that there are more than one currency involved:

cel no Client Currency Amount
a1 x ? 95,000
a2 x ? 80,000
a3 y ? 65,000
a4 z $ 70,000
a5 z $ 20,000
a6 az £ 15,000
--------------------------------
Total ? currency result
$ currency result
£ currency result

pls help

"Bob Phillips" wrote:

Use Subtotal

=SUBTOTAL(9,C2:C7)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Blackwar" wrote in message
...
I have the following data in a worksheet
Client Currency Amount
x ? 95,000
x ? 80,000
y ? 65,000
z $ 70,000
z $ 20,000
az £ 15,000

the issue is if i use filter then the total (sum formula) will not
apply
on
filters it displays the amount of all entries, i can use SUMIF
formula
but
the problem is that there are more than one currency involved (my
criteria
field), so is there any sort of combination of formula to filter
the
list
and
get the desired result.