Hi, experts

I need to sum a column with values if two conditions are met in two
other columns. Let me explain the case with an example:
I have a list of projects (each project belongs to a business area and
a region). I have the projects in column A, the business area in
column B, the region in column C and the value of the project in
column D.

I need to know e.g. What is the value of the projects in a particular
business area in a particular region.

Thanks for your help

Javi

Put the business area you want to find out about in E1, the region in
F1, and this formula in G1:

=SUMPRODUCT((B1:B1000=E1)*(C1:C1000=F1)*(D1:D1000) )

Adjust the ranges to suit, but you can't use complete columns (except
in XL2007).

Hope this helps.

Pete

On Jan 25, 11:01*am, wrote:
Hi, experts

I need to sum a column with values if two conditions are met in two
other columns. Let me explain the case with an example:
I have a list of projects (each project belongs to a business area and
a region). I have the projects in column A, the business area in
column B, the region in column C and the value of the project in
column D.

I need to know e.g. What is the value of the projects in a particular
business area in a particular region.

Thanks for your help

Javi

On Jan 25, 12:06 pm, Pete_UK wrote:
Put the business area you want to find out about in E1, the region in
F1, and this formula in G1:

=SUMPRODUCT((B1:B1000=E1)*(C1:C1000=F1)*(D1:D1000) )

Adjust the ranges to suit, but you can't use complete columns (except
in XL2007).

Hope this helps.

Pete

On Jan 25, 11:01 am, wrote:

Hi, experts

I need to sum a column with values if two conditions are met in two
other columns. Let me explain the case with an example:
I have a list of projects (each project belongs to a business area and
a region). I have the projects in column A, the business area in
column B, the region in column C and the value of the project in
column D.

I need to know e.g. What is the value of the projects in a particular
business area in a particular region.

Thanks for your help

Javi

It doesn´t work. Any idea why?

In what way does it not work? Do you get error messages? Do you get
incorrect values compared to what you would expect? Do you get zero?
Is all your data consistent (i.e. no leading or trailing spaces in the
text)?

Pete

On Jan 25, 11:22*am, wrote:
On Jan 25, 12:06 pm, Pete_UK wrote:





Put the business area you want to find out about in E1, the region in
F1, and this formula in G1:

=SUMPRODUCT((B1:B1000=E1)*(C1:C1000=F1)*(D1:D1000) )

Adjust the ranges to suit, but you can't use complete columns (except
in XL2007).

Hope this helps.

Pete

On Jan 25, 11:01 am, wrote:

Hi, experts

I need to sum a column with values if two conditions are met in two
other columns. Let me explain the case with an example:
I have a list of projects (each project belongs to a business area and
a region). I have the projects in column A, the business area in
column B, the region in column C and the value of the project in
column D.

I need to know e.g. What is the value of the projects in a particular
business area in a particular region.

Thanks for your help

Javi

It doesn´t work. Any idea why?- Hide quoted text -

- Show quoted text -

On Jan 25, 12:39 pm, Pete_UK wrote:
In what way does it not work? Do you get error messages? Do you get
incorrect values compared to what you would expect? Do you get zero?
Is all your data consistent (i.e. no leading or trailing spaces in the
text)?

Pete

On Jan 25, 11:22 am, wrote:

On Jan 25, 12:06 pm, Pete_UK wrote:

Put the business area you want to find out about in E1, the region in
F1, and this formula in G1:

=SUMPRODUCT((B1:B1000=E1)*(C1:C1000=F1)*(D1:D1000) )

Adjust the ranges to suit, but you can't use complete columns (except
in XL2007).

Hope this helps.

Pete

On Jan 25, 11:01 am, wrote:

Hi, experts

I need to sum a column with values if two conditions are met in two
other columns. Let me explain the case with an example:
I have a list of projects (each project belongs to a business area and
a region). I have the projects in column A, the business area in
column B, the region in column C and the value of the project in
column D.

I need to know e.g. What is the value of the projects in a particular
business area in a particular region.

Thanks for your help

Javi

It doesn´t work. Any idea why?- Hide quoted text -

- Show quoted text -
Now it works! Thanks! I have changed the ranges as I tried with
complete columns in the first place.

Which I told you not to do ...

Glad to hear it's working now.

Pete

On Jan 25, 11:48*am, wrote:
Now it works! Thanks! I have changed the ranges as I tried with
complete columns in the first place.

Hi

Basic rules for SUMPRODUCT

All compared ranges must be of same dimension (number of rows).
No full-column references like B:B etc. are allowed
All data in copared column and in conditon field must be in comparable
format. I.e. when in example here in E1 is text entry, in all cells in
C1:B1000 must data be in text format too.

NB! Simple formatting does leave real format for earliy entered values
unchanged. This may be a source for various errors.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


wrote in message
...
On Jan 25, 12:06 pm, Pete_UK wrote:
Put the business area you want to find out about in E1, the region in
F1, and this formula in G1:

=SUMPRODUCT((B1:B1000=E1)*(C1:C1000=F1)*(D1:D1000) )

Adjust the ranges to suit, but you can't use complete columns (except
in XL2007).

Hope this helps.

Pete

On Jan 25, 11:01 am, wrote:

Hi, experts

I need to sum a column with values if two conditions are met in two
other columns. Let me explain the case with an example:
I have a list of projects (each project belongs to a business area and
a region). I have the projects in column A, the business area in
column B, the region in column C and the value of the project in
column D.

I need to know e.g. What is the value of the projects in a particular
business area in a particular region.

Thanks for your help

Javi

It doesn´t work. Any idea why?