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

Chaandni Wrote:
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


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=482782

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

--

HTH

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


"Paul Sheppard"
wrote in message
news:Paul.Sheppard.1y4oam_1131369001.7675@excelfor um-nospam.com...

Chaandni Wrote:
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


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile:
http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=482782

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



"Paul Sheppard" wrote:


Chaandni Wrote:
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


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=482782

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

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



"Paul Sheppard" wrote:


Chaandni Wrote:
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


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile:
http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=482782