How can I use the vlookup function to return a sum of the values?

[Guest]

Hi,

I am trying to use the vlookup function to return several values as 1 result
into 1 single cell. For example, i have a spreadsheet which lists several
different dpeartments. These departments may be repaeted many times in the
spreadsheet. I need a sum of thoses values to be returned in a separate cell
using the vlookup function. Can someone help me on this please?

Thanks

 

[Paul Sheppard]

Hi,

I am trying to use the vlookup function to return several values as 1
result
into 1 single cell. For example, i have a spreadsheet which lists
several
different dpeartments. These departments may be repaeted many times in
the
spreadsheet. I need a sum of thoses values to be returned in a separate
cell
using the vlookup function. Can someone help me on this please?

Thanks

Hi Chaandni

Try This

=SUM(COUNTIF(A1:A12,"dept a")+COUNTIF(A1:A12,"dept b"))

Change the range (A1:A12) to suit your data, add as many dept's to the
formula as required

 

[Bob Phillips]

SUM and + are replicating each, you don't need both.

--

HTH

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


"Paul Sheppard" <[email protected]>
wrote in message

 

[Guest]

Thanks for your message Paul but unfortunatley it does not give the required
soiltion. Maybe if I give you extra detail it may help to resolve the issue.

As an example, say if I want to look up a department X2 in a spreadsheet as
follows and retrun the sum of its head count value as follows:

DEPARTMENT HEAD COUNT
X2 1
X3 05
X2 0.5
X2 1
D4 1
D4 1

As you can see from the example, department x2 appears several times because
it is unique to an employee. I want to return a sum of the headcount in
department X2 eg. 2.5 in a single cell. Currently, when i use the vlookup
function, it returns only the first value eg. 1. How can I use the vlookup
function or any other function so that a sum is returned?

Your help will be greatly appreciated!

Thanks

Chaandni

 

[RagDyeR]

Try this:

=SUMIF(A1:A20,"X2",B1:B20)

You can also reference a cell to contain the dept. you're looking to total,
so that you don't have to change the formula itself to change the dept.:

=SUMIF(A1:A20,C1,B1:B20)
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Thanks for your message Paul but unfortunatley it does not give the required
soiltion. Maybe if I give you extra detail it may help to resolve the issue.

As an example, say if I want to look up a department X2 in a spreadsheet as
follows and retrun the sum of its head count value as follows:

DEPARTMENT HEAD COUNT
X2 1
X3 05
X2 0.5
X2 1
D4 1
D4 1

As you can see from the example, department x2 appears several times because
it is unique to an employee. I want to return a sum of the headcount in
department X2 eg. 2.5 in a single cell. Currently, when i use the vlookup
function, it returns only the first value eg. 1. How can I use the vlookup
function or any other function so that a sum is returned?

Your help will be greatly appreciated!

Thanks

Chaandni