Non programmer needs calculation help!!!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,

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 said:
Hi

I have a spreasheet which shows a column of CLIENTS in Col A, Month_1
Click to expand...
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
Click to expand...
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
Click to expand...
column C will have a value but all records will have at least either or both
column b & C values.
 
Hi
forgot the SUMPRODUCT formula, just add the two SUMIF formulas, if you
want to get the total for both columns

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

Cheer

Malcolm
 
Back
Top