SUMIF with two conditions

[JavierOrange]

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

 

[Pete_UK]

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)*(D11000))

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

Hope this helps.

Pete

 

[JavierOrange]

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)*(D11000))

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

Hope this helps.

Pete

It doesn´t work. Any idea why?

 

[Pete_UK]

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

 

[JavierOrange]

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

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

 

[Arvi Laanemets]

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<at>tarkon.ee )

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)*(D11000))

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

Hope this helps.

Pete

It doesn´t work. Any idea why?

 

[Pete_UK]

Which I told you not to do ...

Glad to hear it's working now.

Pete