Hi

I have a spreasheet which shows a column of CLIENTS in Col A, Month_1 currency in COL B, Month_2 Currency in COL C and COL D states who the client belongs to.

It is laid out like this.

CLIENT MONTH_1 MONTH_2 ADMINISTRATOR

Client 1 2562.33 3162.55 Admin_1
Client 2 1000.00 1050.00 Admin_2
CLient 3 1000.00 950.00 Admin_1

TOTAL TOTAL
4562.33 5162.55

What I would like to do, is place in a cell below the two totals for the columns, another total. However, I would like the calculated total to only show where column D is showing a specific administrator, ie, Admin_1 and leave out any OTHER values that may be in columns B and C.

Not every record in column B will have a value and not every record in column C will have a value but all records will have at least either or both column b & C values.

I hope this clear.

Thanks in advance

Malcolm Davidson

Hi Malcom
if you want to calculate the total for one administrator per column
use:
=SUMIF(D2:D9999,"Admin_1",B2:B9999)
or
=SUMIF(D2:D9999,"Admin_1",C2:C9999)

If you want to total column B and C for one administrator try
=SUMPRODUCT(--(D2:D9999="Admin_1"),(B2:B9999),(C2:C9999))

HTH
Frank


Malcolm wrote:
Hi

I have a spreasheet which shows a column of CLIENTS in Col A, Month_1
currency in COL B, Month_2 Currency in COL C and COL D states who the
client belongs to.

It is laid out like this.

CLIENT MONTH_1 MONTH_2 ADMINISTRATOR

Client 1 2562.33 3162.55 Admin_1
Client 2 1000.00 1050.00 Admin_2
CLient 3 1000.00 950.00 Admin_1

TOTAL TOTAL
4562.33 5162.55

What I would like to do, is place in a cell below the two totals for
the columns, another total. However, I would like the calculated
total to only show where column D is showing a specific
administrator, ie, Admin_1 and leave out any OTHER values that may be
in columns B and C.

Not every record in column B will have a value and not every record
in column C will have a value but all records will have at least
either or both column b & C values.

I hope this clear.

Thanks in advance

Malcolm Davidson

Hi
forgot the SUMPRODUCT formula, just add the two SUMIF formulas, if you
want to get the total for both columns

Frank

Frank Kabel wrote:
Hi Malcom
if you want to calculate the total for one administrator per column
use:
=SUMIF(D2:D9999,"Admin_1",B2:B9999)
or
=SUMIF(D2:D9999,"Admin_1",C2:C9999)

If you want to total column B and C for one administrator try
=SUMPRODUCT(--(D2:D9999="Admin_1"),(B2:B9999),(C2:C9999))

HTH
Frank


Malcolm wrote:
Hi

I have a spreasheet which shows a column of CLIENTS in Col A,
Month_1
currency in COL B, Month_2 Currency in COL C and COL D states who
the
client belongs to.

It is laid out like this.

CLIENT MONTH_1 MONTH_2 ADMINISTRATOR

Client 1 2562.33 3162.55 Admin_1
Client 2 1000.00 1050.00 Admin_2
CLient 3 1000.00 950.00 Admin_1

TOTAL TOTAL
4562.33 5162.55

What I would like to do, is place in a cell below the two totals for
the columns, another total. However, I would like the calculated
total to only show where column D is showing a specific
administrator, ie, Admin_1 and leave out any OTHER values that may
be
in columns B and C.

Not every record in column B will have a value and not every record
in column C will have a value but all records will have at least
either or both column b & C values.

I hope this clear.

Thanks in advance

Malcolm Davidson

Malcolm,

Use this formula in B

=SUMIF($D2:$D4,"Admin_1",B2:B4)

and this in C

=SUMIF($D2:$D4,"Admin_1",C2:C4)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Malcolm" wrote in message
...
Hi

I have a spreasheet which shows a column of CLIENTS in Col A, Month_1
currency in COL B, Month_2 Currency in COL C and COL D states who the client
belongs to.

It is laid out like this.

CLIENT MONTH_1 MONTH_2 ADMINISTRATOR

Client 1 2562.33 3162.55 Admin_1
Client 2 1000.00 1050.00 Admin_2
CLient 3 1000.00 950.00 Admin_1

TOTAL TOTAL
4562.33 5162.55

What I would like to do, is place in a cell below the two totals for the
columns, another total. However, I would like the calculated total to only
show where column D is showing a specific administrator, ie, Admin_1 and
leave out any OTHER values that may be in columns B and C.

Not every record in column B will have a value and not every record in
column C will have a value but all records will have at least either or both
column b & C values.

I hope this clear.

Thanks in advance

Malcolm Davidson

Thanks everybody who sent in help to this problem......working a treat and my boss is impressed

Cheer

Malcolm