How do I return a sum of a range of cells corresponding to a certain criteria

(For example: I want the sum of all cells in A column which have a value in
B colomn:

A B
1 A
3 B
6

- I only want the sum of cells a1, a2 = 4)

Thanks.

=SUMIF(B1:B3,"<",A1:A3)

Daniel Wrote:
How do I return a sum of a range of cells corresponding to a certain
criteria

(For example: I want the sum of all cells in A column which have a
value in
B colomn:

A B
1 A
3 B
6

- I only want the sum of cells a1, a2 = 4)

Thanks.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=319037

On Sat, 20 Nov 2004 11:33:36 -0600, Aladin Akyurek
wrote:


=SUMIF(B1:B3,"<",A1:A3)

or
{=SUM(IF(B1:B3"",A1:A3,0))}

to cover when b3=6

Daniel Wrote:
How do I return a sum of a range of cells corresponding to a certain
criteria

(For example: I want the sum of all cells in A column which have a
value in
B colomn:

A B
1 A
3 B
6

- I only want the sum of cells a1, a2 = 4)

Thanks.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=319037


Steve

Hi

Welcome to the amazing univers of SUMPRODUCT

=SUMPRODUCT((b1:b502)*(A1:A50))
In plain English, sum cells A1 to A50 if in the corresponding cell of
B1 to B50 the value is greater than 2

=SUMPRODUCT((b1:b50<"")*(A1:A50))
In plain English, sum cells A1 to A50 if in the corresponding cell of
B1 to B50 there is something

This is SUMPRODUCT with one criteria but you can have as many criteria
as you need...depending on a value in columns B, C, D, E, F......

See the site below

http://www.excel-vba.com


On Sun, 21 Nov 2004 13:39:39 GMT, (Steve
Walton) wrote:

On Sat, 20 Nov 2004 11:33:36 -0600, Aladin Akyurek
wrote:


=SUMIF(B1:B3,"<",A1:A3)

or
{=SUM(IF(B1:B3"",A1:A3,0))}

to cover when b3=6

Daniel Wrote:
How do I return a sum of a range of cells corresponding to a certain
criteria

(For example: I want the sum of all cells in A column which have a
value in
B colomn:

A B
1 A
3 B
6

- I only want the sum of cells a1, a2 = 4)

Thanks.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=319037


Steve

Pierre Leclerc
http://www.excel-vba.com

Steve Walton Wrote:[color=blue]
On Sat, 20 Nov 2004 11:33:36 -0600, Aladin Akyurek
wrote:


=SUMIF(B1:B3,"<",A1:A3)

or
{=SUM(IF(B1:B3"",A1:A3,0))}

to cover when b3=6
...



You seem to think that "a value" should be restricted to letters/text.
If so, you need something different than what you suggest for it fails
to exclude B3 = TRUE or include B3 housing a formula blank, etc.?


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=319037

Daniel

=SUMIF(B:B,"<",A:A)

Regards

Trevor


"Daniel" wrote in message
...
How do I return a sum of a range of cells corresponding to a certain
criteria

(For example: I want the sum of all cells in A column which have a value
in
B colomn:

A B
1 A
3 B
6

- I only want the sum of cells a1, a2 = 4)

Thanks.